仅从与类别相关的表中获取活动产品

Question

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

Answer 1

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)