Query Join Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Join Problem

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2014-05-24 : 10:03:22

I have a query join problem. This query...

SELECT s.unitweight, i.title2x, i.Authorsx, i.[catalog], o.OrderID, o.OnlineOrderNum, o.AutoIndex, o.RecordID, o.NumCopies, o.Binding, o.ListPrice, o.SalePrice, o.RemoveItem, o.QtyReserved, o.ReservationRevoked, o.AutoIndex, o.Number, o.Additional_Handling
FROM OrderInfo o
INNER JOIN Inventory i ON (i.RecordID = o.RecordID)
INNER JOIN Stock s ON (s.Number = o.Number)
WHERE o.UserID=#Client.UserIdNumber#
AND o.Binding <> 'GiftCert'
AND (o.Number is not NULL or o.Binding = 'Photography' or o.Binding LIKE '%Online Service%')
AND o.NumCopies > 0
ORDER BY o.dateAdded desc

The problem is that some items in my inventory don't have a corresponding record in the Stock table. I still want to return those items with a s.unitweight of Null or 0.
How do I accomplish this?

Thanks in advance.

RichardSteele
Posting Yak Master

160 Posts

Posted - 2014-05-24 : 10:22:12

fixed! Just changed the INNER JOIN Stock s to LEFT JOIN.