CODEWITHSUNDEEP
phpmysql
Francesco
Francesco

Reputation: 25239

MYSQL select category that contains at least two elements from another table

Ecommerce: say I have two tables, one Categories, another Items.

Categories
-------
category_id
category_name

Items
-----
item_id
item_name
category_id

I need to select & list on my page all those Categories that contains at least two items.

I also need to know how many Categories overall i have, so i can paginate... Also, I need to show the number of items under each category if possible with mysql.

so i tried

SELECT  SQL_CALC_FOUND_ROWS *,
(SELECT * FROM items i WHERE i.category_id = c.category_id) as tot
FROM categories c
RIGHT JOIN items i ON i.category_id = c.category_id
HAVING tot > 1
LIMIT 10

but doesn't work... any help is appreciated

Upvotes: 0

Views: 73

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

What you are looking for is a simple aggregation where you group by category and count items.

select 
  c.category_id,
  c.category_name,
  count(*) as items
from categories c
join items i on i.category_id = c.category_id
group by c.category_id
having count(*) >= 2
order by c.category_id
limit @skip, @show;

If you want to know how many categories match your condition:

select count(*)
from categories c
where 
(
  select count(*)
  from items i 
  where i.category_id = c.category_id
) >= 2;

Upvotes: 1

Related Questions