Help With Left Join - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Help With Left Join

Author

Topic

Nick
Posting Yak Master

155 Posts

Posted - 2002-09-23 : 10:45:47

Hello-

I have a little problem that I think I can solve with a left join, but I can't quite figure out the correct syntax for it.

Here is my original query.

---
SELECT DISTINCT
tblProductType.productTypeID, tblProducts.itemID, tblInventory.lowLevel, tblProducts.pictureLocation, tblProducts.shortDesc,
tblProducts.lowInventoryDate, tblProducts.minOrderQty, tblInventory.itemQty, tblProducts.listRanking
FROM tblProducts INNER JOIN
tblProductType ON tblProducts.productTypeID = tblProductType.productTypeID INNER JOIN
tblInventory ON tblProducts.itemID = tblInventory.itemID
WHERE (tblProductType.divisionID IN
(SELECT divisionID
FROM tblLogins_Divisions
WHERE userID IN
(SELECT userID
FROM tblLogins
WHERE loginUsername = 'johndoe@anonymous.com'))) AND (tblProducts.customerID = 18) AND (tblProducts.itemID IN
(SELECT DISTINCT itemID
FROM tblInventory))
ORDER BY tblProductType.productTypeID, tblProducts.listRanking, tblProducts.itemID
---

This query pulls out all of the fields from tblProducts from all of the rows that meet the criteria. Recently I've added a new table that has extra info about some items, but not all. I need a query that adds this column to the query, but still pulls out all of the rows regardless if the item exists in the new table or not. The tables would be joined on 'itemID'. Can anyone offer a little help?

Thanks.

ankurgupta26
Starting Member

32 Posts

Posted - 2002-09-23 : 10:55:04

Try this.....

SELECT DISTINCT
tblProductType.productTypeID, tblProducts.itemID, tblInventory.lowLevel, tblProducts.pictureLocation, tblProducts.shortDesc,
tblProducts.lowInventoryDate, tblProducts.minOrderQty, tblInventory.itemQty, tblProducts.listRanking,
NewTable.colname
FROM
tblProducts INNER JOIN tblProductType
ON tblProducts.productTypeID = tblProductType.productTypeID

INNER JOIN tblInventory
ON tblProducts.itemID = tblInventory.itemID

LEFT OUETER JOIN NewTable
ON tblProducts.itemID = NewTable.itemid

WHERE (tblProductType.divisionID IN
(SELECT divisionID
FROM tblLogins_Divisions
WHERE userID IN
(SELECT userID
FROM tblLogins
WHERE loginUsername = 'johndoe@anonymous.com'))) AND (tblProducts.customerID = 18) AND (tblProducts.itemID IN
(SELECT DISTINCT itemID
FROM tblInventory))
ORDER BY tblProductType.productTypeID, tblProducts.listRanking, tblProducts.itemID

Thanks !
AnkuR.

ankurgupta26
Starting Member

32 Posts

Posted - 2002-09-23 : 10:55:05

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-23 : 10:56:50

just add a

left outer join newtbl on tblProducts.itemID = newtbl.itemID

SELECT DISTINCT
tblProductType.productTypeID, tblProducts.itemID, tblInventory.lowLevel, tblProducts.pictureLocation, tblProducts.shortDesc,
tblProducts.lowInventoryDate, tblProducts.minOrderQty, tblInventory.itemQty, tblProducts.listRanking
FROM tblProducts
INNER JOIN tblProductType
ON tblProducts.productTypeID = tblProductType.productTypeID
INNER JOIN tblInventory
ON tblProducts.itemID = tblInventory.itemID
left outer join newtbl
on tblProducts.itemID = newtbl.itemID
WHERE (tblProductType.divisionID IN
(SELECT divisionID
FROM tblLogins_Divisions
WHERE userID IN
(SELECT userID
FROM tblLogins
WHERE loginUsername = 'johndoe@anonymous.com'))) AND (tblProducts.customerID = 18) AND (tblProducts.itemID IN
(SELECT DISTINCT itemID
FROM tblInventory))
ORDER BY tblProductType.productTypeID, tblProducts.listRanking, tblProducts.itemID

Are you sure you need all the in clauses in the where clause?
Couldn't they be done by inner joins.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Nick
Posting Yak Master

155 Posts

Posted - 2002-09-23 : 10:57:39

Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources