[RESOLVED] Show all possible items in one table -- outer join thing?

[kows]

This is a question pertaining to MSSQL 2008. Okay, I'm not sure how much information I have to give in order to get the point across. The basic idea is that I have a Stock table that holds 40 different items that can be added to an order for an outlet (store). I need to return all 40 stock items even if these stock items don't appear as on an order already, which is where I have a problem. I thought I could just outer join the Stock table or something, but I'm either not thinking clearly or just not seeing this. This is the query I've created to test, which returns only 27 records:

SELECT s.StockId
FROM OutletOrder oo
INNER JOIN OrderItem oi ON oo.OrderId=oi.OrderID
LEFT OUTER JOIN Stock s on oi.StockID=s.StockID
WHERE oo.OutletId=8 AND oo.RecieveDate IS NULL;

and, for testing purposes, this is a dump of the relevant tables and some data: http://davidmil.es/sql/dump.sql (~115k)

I would greatly appreciate any help!

[jmcilhinney]

You say that you want every stock item but you have the Stock table on the right-hand side of a LEFT OUTER JOIN. A LEFT OUTER JOIN includes every record from the left-hand table and a RIGHT OUTER JOIN includes every record from the right-hand table.

[kows]

Thanks for the reply. Changing that join to a right outer join doesn't produce the desired result; I still only retrieve 27 records.

Any other insight?

[si_the_geek]

You need to also alter the Where clause so that it doesn't force data to come from the oi/oo tables, eg:

WHERE (oo.OutletId=8 AND oo.RecieveDate IS NULL) OR (oo.OutletId Is Null);

I personally would re-write it so that the table you always want data from is listed first in the From clause, eg:

SELECT s.StockId
FROM Stock s 
LEFT OUTER JOIN OrderItem oi ON oi.StockID=s.StockID
LEFT OUTER JOIN OutletOrder oo ON oo.OrderId=oi.OrderID
WHERE (oo.OutletId=8 AND oo.RecieveDate IS NULL) OR (oo.OutletId Is Null);

[kows]

Thanks for the reply again!

I personally would re-write it so that the table you always want data from is listed first in the From clause, eg:

SELECT s.StockId
FROM Stock s 
LEFT OUTER JOIN OrderItem oi ON oi.StockID=s.StockID
LEFT OUTER JOIN OutletOrder oo ON oo.OrderId=oi.OrderID
WHERE (oo.OutletId=8 AND oo.RecieveDate IS NULL) OR (oo.OutletId Is Null);

I did this before because it made more sense to me, but I kept getting the same results. So I left it alone.

You need to also alter the Where clause so that it doesn't force data to come from the oi/oo tables, eg:

WHERE (oo.OutletId=8 AND oo.RecieveDate IS NULL) OR (oo.OutletId Is Null);

oo.OutletId isn't a nullable field, so OutletId will never be null, and neither is oi.OutletId/oi.StockId. Changing this doesn't do anything, but thank you for the suggestion.

Would anyone be open to running the SQL file I've provided in order to play around with this? Thanks again in advance!

[kows]

Would anyone happen to have any insight on this? I've still not been able to produce a desired result after much playing around.

[dee-u]

Not sure what you are trying to do, are you just trying to get to StockID's? What are you trying to get from the other tables? Do you just want to return 40 items?

[kows]

I need to return every StockID that exists in the Stock table, regardless of if that StockID exists in an OrderItem for an Order. So, 40 items need to be returned every time. The quantity and prices in the order and some other things will be calculated later on, but I simplified the query for posting on this forum.

[GaryMazzone]

So why not just just select the StockId from the stock table? That will return all wither or not they have ever been ordered

[dee-u]

So why not just just select the StockId from the stock table? That will return all wither or not they have ever been ordered

My thoughts also.

[kows]

But along with the stock items, the order information is needed to be displayed. What you're suggesting, after all, is what this query already does:

SELECT s.StockId
FROM Stock s 
LEFT OUTER JOIN OrderItem oi ON oi.StockID=s.StockID
LEFT OUTER JOIN OutletOrder oo ON oo.OrderId=oi.OrderID
WHERE (oo.OutletId=8 AND oo.RecieveDate IS NULL) OR (oo.OutletId Is Null);

I'm not sure if you're not understanding the problem, or what, but I solved my own problem by only joining the OutletOrder table on multiple conditions (essentially, move WHERE clause to JOIN clause):

LEFT OUTER JOIN OutletOrder oo
    ON oo.OrderId=oi.OrderID
        AND oo.OutletId=8
        AND oo.RecieveDate IS NULL