Linking inventory master table to sales order history

[awholtsIT]

I want to link my inventory master to my sales history to display sales by item (sku). The issue is, I want to return all items from the inventory master, even if they have no sales. (Not have a corresponding record in the sales history table).

Once I link the two tables, all items without sales are not in the record set. I've tried various joins, even switching which table is referenced first.

Again, if my inventory master has 100 item records, I want my final sales report (by item) to show all 100 items, even if some items have no records in the sales history.

How do I accomplish this?

Appreciate your input in advance.

Thanks,

Andrew

 

[RiverGuy]

You use an OUTER JOIN

SELECT *
FROM Master
LEFT OUTER JOIN Sales ON Master.sku = Sales.sku

 

[gmmastros]

You need to use a left join to accomplish this. However, you need to be careful with your where clause. If it includes columns from the RIGHT table in the LEFT join, you will effectively get an INNER join. Sound complicated?

[tt][blue]Select *
From LeftJoinTable
Left Join RightJoinTable
On LeftJoinTable.sku = RightJoinTable.sku
Where RightJoinTable.OtherColumn = 'Blue'
[/blue][/tt]

The above query would behave like it's an inner join because your where clause has a condition on the RightJoinTable. To correct this, you should move the condition to the ON clause, like this:

[tt][blue]Select *
From LeftJoinTable
Left Join RightJoinTable
On LeftJoinTable.sku = RightJoinTable.sku
And RightJoinTable.OtherColumn = 'Blue'
[/blue][/tt]

This query would return all rows from the LeftJoinTable.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom