Need to select no product categories

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

Need to select no product categories

Author

Topic

GaryNull
Starting Member

14 Posts

Posted - 2014-01-19 : 12:37:32

I want to get a list of any Categories
where ALL the products in that Category
are not published (Published = 0).
(I want to get the Categories where no products are listed for it)
Here are the tables, not sure where to begin :

SELECT [Id], Published
FROM Product
WHERE Published = 0

SELECT [Id] ,[Name]
FROM Category

SELECT [Id] ,[ProductId] ,[CategoryId]
FROM Product_Category_Mapping

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-19 : 12:44:09

[code]
SELECT Id,Name
FROM Categories c
WHERE NOT EXISTS(
SELECT 1
FROM Product_Category_Mapping pcm
INNER JOIN Product p
ON p.Id = pcm.ProductId
WHERE p.Published = 1
AND pcm.CategoryId = c.Id
)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

GaryNull
Starting Member

14 Posts

Posted - 2014-01-19 : 13:20:21

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-20 : 06:24:56

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources