[resolved] Select where not in related table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

[resolved] Select where not in related table?

Author

Topic

Angate
Starting Member

24 Posts

Posted - 2009-01-19 : 10:56:35

I have two tables, productTable and productComponentTable. productTable lists product information, while productComponentTable lists relationships between products that make up other products.

For example: nuts, bolts, and washers are each a separate product. I want a hardware pack that contains a few of each to also be a product. The hardware pack would be the parentProductID, and the nut/bolt/washer would be the childProductID, and the quantity is how many of the child is contained within the parent.

What I am trying to return is a list of all productID, productPIN and productName that do not correspond as a childProductID to the provided parentProductID. The reason is I am making a VB.Net interface for a user to add components to a product, but do not want the product's current components to show up in the list, so that they don't duplicate.

This is what I have, and it obviously does not work.

@parentID

SELECT productID, productPIN, productName
FROM productTable
LEFT OUTER JOIN productComponentTable
ON productTable.productID = productComponentTable.parentProductID
WHERE productComponentTable.parentProductID <> @parentID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-19 : 11:02:31

[code]
SELECT p.productID, p.productPIN, p.productName
FROM productTable p
LEFT OUTER JOIN productComponentTable pc
ON p.productID = pc.childProductID
AND pc.parentProductID = @parentID
WHERE pc.productID IS NULL
AND p.productID<>@parentID
[/code]

Angate
Starting Member

24 Posts

Posted - 2009-01-19 : 15:58:37

I get an error on line 6 saying "invalid column name 'productID'". And when I remove that line, I get all columns from productTable except the parentID one. What I want is to supply, say product 4, and only have products 5,6,7, and 8 returned, because they are not product 4, and are not associated with it in the productComponentTable.

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-01-19 : 16:24:05

Hello !

As Mr.Visakh wrote, he might have just overlooked the column name or the alias. So, replace Pc.ProductId with ChildProductId or ParentProductid. That line is vital to this query & he is usually very very thorough.

regards,
Anil Kumar.

SELECT p.productID, p.productPIN, p.productName
FROM productTable p
LEFT OUTER JOIN productComponentTable pc
ON p.productID = pc.childProductID
AND pc.parentProductID = @parentID
WHERE pc.parentProductID IS NULL
-- or WHERE pc.childProductID IS NULL
-- or WHERE p.ProductID IS NULL

AND p.productID<>@parentID

Angate
Starting Member

24 Posts

Posted - 2009-01-20 : 00:03:21

It was "WHERE pc.childProductID IS NULL"

THANK YOU SO MUCH!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources