CODEWITHSUNDEEP
phpmysqlsql
Kenny
Kenny

Reputation: 144

Only get active products from table related to category

I got 2 tables:

1 Category
- cID
- Name
- Active

2 Products
- pID
- Name
- category_id
- active

This is want i want: I want a list from categories that ONLY have ACTIVE products.

Thanks in advance

SOLUTION: SELECT DISTINCT category.* FROM category INNER JOIN products ON category.id = products.c_id WHERE products.active = 0 ORDER BY category.id DESC

Upvotes: 1

Views: 552

Answers (3)

roman
roman

Reputation: 117380

I'd suggest construction like this
IMHO, it's syntax shows what you really want to see - all categories that have active products

select C.cID, C.Name, C.Active
from Category as C
where C.cID in (select distinct T.category_id from Products as T where T.Active = 1)

Upvotes: 1

xdazz
xdazz

Reputation: 160833

Assume Products.Active = 1 means active state.

SELECT c.*
FROM Category c
INNER JOIN Products p ON c.CID = p.category_id
GROUP BY c.CID
HAVING COUNT(*) = SUM(IF(p.Active = 1, 1, 0))

Upvotes: 3

Rick Calder
Rick Calder

Reputation: 18695

SELECT * FROM Products
Join Category on Category.ciD = Products.category_id
WHERE Category.Active = 1 AND Products.Active =1

This is assuming your active columns are just 1 or 0. Otherwise adjust according to what values you're storing there.

Upvotes: 1

Related Questions