mssql how to join two tables by multiple columns Edit

Murugan Andezuthu Dharmaratnam | 18 January 2021 | 150

Given tables PurhcaseOrder & Received with Columns as shown below. I would like to join the table with multiple columns PONumber & POLine. How can this be done.

TABLE : PurchaseOrder
 
POId  Name   PONumber  POLine   Quantity
---------------------------------------------------------
1       hello       10                1             10
2       world       12                1              20
3       hello        10               2              30

TABLE : Received

ReceivedId    PONumber   POLIne  Quantity
------------------------------------------------------------
1                     10                 1            10
2                     10                 2            30

Solution

You can use an and while joining the tables to join using two columns

select Purchase.*,Received.Quantity as ReceivedQuantity from PurchaseOrder left join Received on PurchaseOrder.PONumber = Received.POLIne  AND PurchaseOrder.POLIne = Received.POLine