Simple Join ..... How Degrading 1

[TechieTony]

Morning.

So.... ive been working for about 30 minutes on this Join between 2 tables and I just cant seem to get this correct.

Table 1: dbo.McCoy_Inactive_Item_Report

item_id Location_id Date
0107-12-12 3000 6/13/2008 PM
0107-12-16 3000 6/17/2008 PM
0107-17-19 3000 6/15/2008 PM
0107-12-11 3000 6/18/2008 PM
0107-18-10 3000 6/13/2008 PM
0107-16-18 3000 6/19/2008 PM

Table 2: dbo.McCoy_Inactive_Item_Report_TR

item_id Location Date
0107-12-12 3000 6/17/2008 PM
0107-12-16 3000 6/19/2008 PM
0107-17-19 3000 6/12/2008 PM
0107-12-11 3000 6/15/2008 PM

I would just like to Join These Tables so that I return all of the records in the first table plus any fields matching records in the second table.

The first table has 4000 records and my second has 18 and right now my join will show matching records from both = 18. What im really going after is a table that will combine my two views with results that look like this:

item_id Location_id Date
0107-12-12 3000 6/13/2008 PM
0107-12-12 3000 6/17/2008 PM
0107-12-16 3000 6/17/2008 PM
0107-17-19 3000 6/12/2008 PM
0107-17-19 3000 6/15/2008 PM
0107-12-11 3000 6/18/2008 PM
0107-12-11 3000 6/15/2008 PM
0107-18-10 3000 6/13/2008 PM
0107-16-18 3000 6/19/2008 PM
0107-12-16 3000 6/19/2008 PM

I have no idea why this has been so troubling... I sux at SQL badly

Noncentz

My Query
---------------------------------
SELECT dbo.McCoy_Inactive_Item_Report.item_id, dbo.McCoy_Inactive_Item_Report.last_sale_date, dbo.McCoy_Inactive_Item_Report.location_id, dbo.McCoy_Inactive_Item_Report_TR.item_id AS item_id2, dbo.McCoy_Inactive_Item_Report_TR.from_location_id, dbo.McCoy_Inactive_Item_Report.qty_on_hand, dbo.McCoy_Inactive_Item_Report_TR.Last_Date_Transferred


FROM
dbo.McCoy_Inactive_Item_Report INNER JOIN dbo.McCoy_Inactive_Item_Report_TR
ON dbo.McCoy_Inactive_Item_Report.item_id = dbo.McCoy_Inactive_Item_Report_TR.item_id AND dbo.McCoy_Inactive_Item_Report.location_id = dbo.McCoy_Inactive_Item_Report_TR.from_location_id

 

[RiverGuy]

This is where you use a UNION.

SELECT Column1, Column2, Column3 FROM Table1
UNION
SELECT Column1, Column2, Column3 FROM Table2

Also, look up information on UNION ALL.

 

[TechieTony]

Im on it... thx again RiverGuy