<div>
<i>An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each recordeven if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).<br />
<br />
(For a table to qualify as left or right its name has to appear after the FROM or JOIN keyword, respectively.)</i> &nbsp;- Wikipedia entry for Join (SQL)<br />
<br />

</div>


<div>
your request is looking for a product that has an order item id of 1123.<br />
<br />
if you want to pull all the products in the order 1123 reverse your join (and make sure there isn't only 1 product in it)<br />
<br />
SELECT Products.ProductId, Products.ProductDescriptio<wbr />n, OrderItems.Quantity<br />
FROM OrderItems<br />
LEFT JOIN Products<br />
on ProductId<br />
where <br />
OrderItems.orderID = '1123'<br />

</div>


<div>
Meh, I must have misread something. You should try a RIGHT OUTER JOIN instead.<br />

</div>


<div>
I want to pull in all Products, and then I want to pull in the quantity field from OrderIterms where the OrderID matches '1123'.
</div>


<div>
Perhaps explaining why I want to do this would help.<br />
<br />
I want to display all the products in a datagrid, but I want to allow the user to edit the quantity for a given order. So to avoid doing some crazy code checking, if I can fill the datagrid with all the products and the quantity of any orderitems, then I can default all the other quantities to 0. Thus getting around the issue of having to do alot of looping.
</div>


<div>
Sorry CCongdon,<br />
<br />
Missed you post as I was busy typing.<br />
<br />
:)<br />
<br />
Mark.
</div>


<div>
Heh, don't worry. I've had several times where I type an answer in and when the screen refreshes I see someone posted the answer just above :) It does make me feel a little better though seeing a confirmation of my own answer!
</div>


<div>
Same here,<br />
<br />
I spend so long trying to decide if my answer will make me look stupid or not, that by the time I get brave enough to post it, someone has beat me to it.<br />
<br />
:)<br />
<br />
Glad we were thinking along the same lines though.<br />
<br />
Mark
</div>


<div>
LOL! That's funny. I've got the same problem too. I'll even sometimes go into an editor and try the code first before posting it. By the time I mess with everything I sometimes see that someone else has posted it.<br />
You'll be happy to know that the code we both put forward does work in SQL Management Studio :)<br />

</div>


<div>
Thanks for letting me know, &nbsp;I'm trying to use this to build on my SQL skills. &nbsp;Niece to get one right for a change.
</div>


<div>
Your code is pretty much identical to mine except that I have a tendency to explicity state things (Like 'AS' and 'OUTER') and my code worked on SQL Server '05. In theory, the query should even work on any DB system that supports nested SQL. My IsNull contruct though would probably need to be coded specifically for the DB in question (such as IfNull for MySQL)
</div>


<div>
Thanks for the pointers,<br />
<br />
as teh login name suggests, every day is a school day!<br />
<br />
Cheers,<br />
<br />
Mark.<br />
<br />

</div>


<div>
Thanks for the help lads, and sorry about the delay in getting back to you with the points. Turns out that the above doesn't work in SQL CE so I had to do it in code, but it works fine in SQL Server 2005.<br />
<br />
Cheers
</div>


<div>
Glad we could help!<br />
<br />
Mark.
</div>


<div>
My understanding is that SQL CE only supports sub-queries in the FROM clause, which, if true, would explain why the initial query doesn't run there. If that's the case, I think the following query would work in SQL CE.<br />
<br />

<pre><code class="code-1 nolinks" id="code-122744"><span>SELECT Products.ProductId,</span>
<span>       Products.ProductDescription,</span>
<span>       OrderItems.Quantity</span>
<span>FROM (SELECT * FROM OrderItems WHERE orderID = '1123) AS OrderItems</span>
<span>RIGHT OUTER JOIN tProducts ON tProducts.ProductId=OrderItems.ProductId</span>
</code></pre>
</div>


<div>
<div class="content wysiwyg-content">
I have two tables<br />
<br />
OrderItems<br />
&amp;<br />
Products<br />
<br />
I am trying to do a join on these two tables where i display all the products from the Products Table but I do a join where I use the OrderID to find the OrderItems, and then display the Quantity from this this table.<br />
<br />
This is what I have:<br />
<br />
SELECT Products.ProductId, Products.ProductDescriptio<wbr />n, OrderItems.Quantity<br />
FROM tProducts<br />
LEFT JOIN OrderItems<br />
ON tProducts.ProductId=OrderI<wbr />tems.Produ<wbr />ctId where OrderItems.orderID = '1123'<br />
<br />
<br />
Which does return<br />
<br />
ProductID, ProductDescription, Quantity<br />
<br />
but it is currently only showing the 1 single OrderItem which has '1123'<br />
<br />
Instead of all Products and the Quantity for the orderitem with orderid 1123.
</div>
</div>


I have two tables

OrderItems
&
Products

I am trying to do a join on these two tables where i display all the products from the Products Table but I do a join where I use the OrderID to find the OrderItems, and then display the Quantity from this this table.

This is what I have:

SELECT Products.ProductId, Products.ProductDescription, OrderItems.Quantity
FROM tProducts
LEFT JOIN OrderItems
ON tProducts.ProductId=OrderItems.ProductId where OrderItems.orderID = '1123'


Which does return

ProductID, ProductDescription, Quantity

but it is currently only showing the 1 single OrderItem which has '1123'

Instead of all Products and the Quantity for the orderitem with orderid 1123.

Help with a left join

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.