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

Select where not in related table?

Author

Topic

Angate
Starting Member

24 Posts

Posted - 2009-01-21 : 14:35:33

I asked a similar question earlier this week, but was unable to reverse engineer the answer I got for that to fit this.

I have 3 tables. productTable, categoryTable, and productCategoryTable. productTable holds products, categoryTable stores categories, and productCategoryTable stores the relations between them (IE: what categories a product is in) What I am trying to do is come up with a SELECT statement that will return all categories the provided productID is NOT in as per the productCategoryTable.

With the example data above, If I provide the productID 23, I would want categories 1,3,4,6 to be returned, because product 23 is in categories 2 and 5.

This is the SQL I have:

(
@productID int
)

AS
SET NOCOUNT ON
SELECT DISTINCT c.categoryID, c.categoryName
FROM categoryTable c
LEFT OUTER JOIN productCategoryTable pc
ON c.categoryID = pc.categoryID
WHERE pc.productID <> @productID
RETURN

That Query returns 2,5 and 6...

Thank You Very Much,

Dan

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-21 : 14:54:39

Maybe:


	SELECT DISTINCT c.categoryID, c.categoryName
	FROM categoryTable c
	LEFT OUTER JOIN productCategoryTable pc
	ON c.categoryID = pc.categoryID
	WHERE pc.categoryID is null and pc.productID = @productID

Angate
Starting Member

24 Posts

Posted - 2009-01-21 : 15:34:48

That returns no rows

vinoo128
Starting Member

9 Posts

Posted - 2009-01-21 : 16:17:58

select categoryId,categoryName
from category
where categoryId not in (select category_id
from productCategory
where productId=@productId)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 09:08:33

[code]
SELECT DISTINCT c.categoryID, c.categoryName
FROM categoryTable c
LEFT OUTER JOIN productCategoryTable pc
ON c.categoryID = pc.categoryID
GROUP BY c.categoryID, c.categoryName
HAVING SUM(CASE WHEN pc.ProductId=@ProductID THEN 1 ELSE 0 END) =0
[/code]

Angate
Starting Member

24 Posts

Posted - 2009-01-22 : 11:24:42

Thank You! Both sodeep's and visakh 16's work. Is there a benefit of one over the other? is there a way to time the execution of the stored procedure?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 11:34:32

quote:
Originally posted by Angate

Thank You! Both sodeep's and visakh 16's work. Is there a benefit of one over the other? is there a way to time the execution of the stored procedure?

compare both using profiler. also use below modified one instead of my last one (actually distinct is not needed as we're already grouping by, i didnt notice it while i copy pasted)


SELECT c.categoryID, c.categoryName
FROM categoryTable c
LEFT OUTER JOIN productCategoryTable pc
ON c.categoryID = pc.categoryID
GROUP BY c.categoryID, c.categoryName
HAVING SUM(CASE WHEN pc.ProductId=@ProductID THEN 1 ELSE 0 END) =0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources