CODEWITHSUNDEEP
phpmysql
Fuxi
Fuxi

Reputation: 7589

tricky mysql query with categories

here's my data structure:

category_main
id  name
---------------------
1   catmain1  
2   catmain2

category_sub
id  name     id_catmain
---------------------
1   catsub1  1
2   catsub2  1
3   catsub3  2

images
id  name     catsub
---------------------
1   image1   1
2   image2   1
3   image3   2
4   image4   3

desired output:

id_catmain  catmain    images_total
--------------------------------------------------
1           catmain1   3
2           catmain2   1

the problem is getting the total amount of images per main category .. i tried something like (as a view)

select categories.*, group_concat(id) as all_cat from categories group by id_catmain

then querying that view using FIND_IN_SET .. but i think there must be a better way using one query only. any ideas? thanks

Upvotes: 0

Views: 40

Answers (2)

Ricardo Souza
Ricardo Souza

Reputation: 16446

You can solve this in many ways. One of these would be to use a subquery:

SELECT c.id AS id_catmain, c.name AS catmain,
  (SELECT COUNT(i.id) AS totalImages
  FROM images i
  INNER JOIN category_sub s ON i.catsub = s.id
  WHERE s.id_catmain = c.id) as totalImages
FROM category_main c
ORDER BY c.name ASC;

Sample here: http://sqlfiddle.com/#!9/d78e2/5

This is not better than Bun's answer, it's just to say there are other ways of doing this.

Upvotes: 1

Bun
Bun

Reputation: 128

Something along these lines should work I think:

SELECT c.id, c.name, COUNT(*) AS images_total FROM images i
JOIN category_sub cs ON cs.id = i.catsub
JOIN category_main c ON c.id = cs.id_catmain
GROUP BY c.id

What you are basically doing there is tying all the image table's rows to the subcategories they represent, then tying the main categories to the same row through the subcategory id. Then you can simply count all the rows, grouping the amount of counted rows by all the different main category ids.

Upvotes: 2

Related Questions