Search code examples
sql-server-2008joinleft-joinright-join

How to join two tables to select all data with and without the condition in MsSQL

I have two tables.

Table_Sale

S_Date
S_Store
S_Item_ID
S_Qty

Table_Return

R_Date
R_Store
R_Item_ID
R_Qty

Imagine Table_Sale have 1000 row and Table_Return have 250 rows.I want to do
this cindition.
(S_Date=R_Date and S_Store=R_Store and S_Item_ID=R_Item_ID)


Think there are 150 rows match with that condition. Then there are 850 Rows from Table_Sale and 100 row in Table_Return which are not matching with the condition. Now I want 150+100+850 all data in a one table. How can I make the join sir.?Please anyone help me.

Solution

  • You should use a FULL OUTER JOIN. Something like this...

    SELECT *
FROM Table_Sale a
FULL OUTER JOIN Table_Return b ON a.S_Date = b.R_Date 
                              and a.S_Store = b.R_Store
                              and a.S_Item_ID = b.R_Item_ID