Skip to content
Advertisement

How to work left outer join in SQl Server?

First: I know to use all types of join but I don’t know why it works like this for this Query

I have a Scenario for making a SQL Query, by using 3 tables and a left outer join between selling and order items.

My Tables:

--------------------
   Item 
--------------------
ID      |  Code
--------------------
1       |  7502

SQL > select * from Item where id = 1
---------------------

   Item_Order
---------------------------
Item   |  Box   |   Quantity
---------------------------
1      | 30     |  15000
1      | 12     |  6000
SQL > select * from Item_Order where Item = 1
--------------------------

   Invoice_Item
-------------------
Item  |  Num  |  Quantity
-------------------------
1     | 1.64  | 10
1     | 2.4   | 8
SQL > select * from Invoice_Item where Item = 1

I want this output:

Item  | OrderQ  | OrderB | SellN | SellQ
-----------------------------------------
1     | 1500    | 30     |  1.64 | 10
1     | 6000    | 12     |  2.4  | 8

My SQL code:

SELECT  Item.ID, Item_Order.Box As OrderB, Item_Order.Quantity As OrderQ, Invoice_Item.Num As SellN, Invoice_Item.Quantity As SellQ
FROM Item LEFT OUTER JOIN 
     Invoice_Item ON Item.ID = Invoice_Item.Item LEFT OUTER JOIN 
     Item_Order ON Item_Order.Item = Item.ID  
where Item.ID = 1

Out put My Code

Why is my output 2x? or why does my output return 4 records?

Advertisement

Answer

Your result can be achieve with row_number:

select a.ID
       , a.OrderB
       , a.OrderQ
       , b.Quantity SellQ
       , b.Num SellN
from 
(SELECT Item.ID
       , Item_Order.Box As OrderB
       , Item_Order.Quantity As OrderQ
       , row_number () over (order by Item.ID) rn
FROM Item 
left outer JOIN Item_Order ON Item.ID = Item_Order.Item) a
left outer join (select Item
                        , Num
                        , Quantity
                        , row_number () over (order by Item) rn 
                 from Invoice_Item ) b
on a.ID = b.Item
and a.rn = b.rn

Here is a demo

You can add more tables like this:

left outer join (select Item
                            , Num
                            , Quantity
                            , row_number () over (order by Item) rn 
                     from Invoice_Item ) b
2 People found this is helpful
Advertisement