CODEWITHSUNDEEP
sql-serverjoin
Rajeev Kumar
Rajeev Kumar

Reputation: 4963

Joining of two tables in SQL Server

I have two tables which I would like to join.

Tbl_1

RouteNo | StopID | VehicleID | RouteID | VehicleNo | TicketCount | Revenue
--------------------------------------------------------------------------
Route_1      0        5           2          DL7S        54         10000

TBL_2

StopID | Stop         | RouteID | VehicleID | Passengers
---------------------------------------------------------
 5       Stop_5         2         5           50
 0       Unknown        2         5           100
 4       Stop_4         2         5           90

Now I would like to join two table to get the result like this:

Stop      | RouteNo | VehicleNo | TicketCount | Revenue | Passengers
--------------------------------------------------------------------    
Stop_5       Route_1    DL7S         0           0            50
Unknown      Route_1    DL7S         54          10000        100                       
Stop_4       Route_1    DL7S         0           0            90

I have tried the following

Select 
    a.RouteNo, b.Stop, a.VehicleNo, a.TicketCount, a.Revenue, b.Passengers 
from 
    TBL_1 a
full Join 
    TBL_2 b on a.StopID = b.StopID 
            and a.VehicleID = b.VehicleID 
            and a.RouteID = b.RouteID

Actual result:

Stop      | RouteNo | VehicleNo | TicketCount | Revenue | Passengers
---------------------------------------------------------------------    
Stop_5       Null       Null         Null          Null       50
Unknown      Route_1    DL7S         54            10000      100                       
Stop_4       Null       Null         Null          Null       90

Upvotes: 1

Views: 62

Answers (2)

Sam Bauwens
Sam Bauwens

Reputation: 1387

Start your query with TBL_2 instead of Tbl_1 :

Select a.RouteNo, b.Stop, a.VehicleNo, a.TicketCount, a.Revenue, b.Passengers 
from TBL_2 b
Inner Join TBL_1 a
on a.StopID = b.StopID and a.VehicleID = b.VehicleID and a.RouteID = b.RouteID

You can also use the or keyword in the on statement if you need to match your rows if "at least one of the values must match" :

Select a.RouteNo, b.Stop, a.VehicleNo, a.TicketCount, a.Revenue, b.Passengers 
from TBL_2 b
Inner Join TBL_1 a
on a.StopID = b.StopID or a.VehicleID = b.VehicleID or a.RouteID = b.RouteID

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You have to use COALESCE:

Select COALESCE(a.RouteNo, b.RouteNo), 
       COALESCE(b.Stop, a.Stop),
       COALESCE(a.VehicleNo, b.VehicleNo),
       COALESCE(a.TicketCount, b..TicketCount),
       COALESCE(a.Revenue, b..Revenue),
       COALESCE(b.Passengers, a.Passengers)
from TBL_1 a
full Join TBL_2 b
on a.StopID = b.StopID and 
   a.VehicleID = b.VehicleID and 
   a.RouteID = b.RouteID

Upvotes: 1

Related Questions