SQL Query join to include Null values where no item in second table

[Eagletalon]

Hey guys,

Alright I have a tricky assignment to do in a view that will enable my next program to run MUCH faster and more reliable...

Now I need to list all Items (1 table) and their locations where they are stored (2nd table) in a way that enables 1 to transfer...

Problem is the second table does not contain all the necessary locations as the previous programs would delete a location entry once it hit 0...

So how do I do the following:

Table1:
<PRE>
Item ID
1. X
2. Y
</PRE>
Table 2:
Item ID Location Quantity
X A 5
Y B 3

Desired View:

Item ID Location Quantity
X A 5
X B 0
Y A 0
Y B 3

I realize that simply asking a solution without trying is not the aim of this forum... but I'm really at my wits end and I've tried all kinds of join Statements I know,

I dont think it will be too difficult, but I am just missing something

Would really appreciate a quick solution, thnx guys

[Eagletalon]

Nevermind cant get the spacing right

[adam_k]

Just for the record, you can do what you are seeking by introducing a third (which will hold all locations) in your join.
You need to inner join items with locations (or the available locations for each item if in your scope) and then left join this to table2 using both item and location as keys. In your select you should either select sum by quantity and group by item and location or select item, location, isnull(quantity,0).