mysql query with info from 3 different tables

Question

I have an online store, and the products database is setup like below. What I want to do is to form a query that will print main categories and number of products inside them, ordered by fewest to most products. Is that possible without doing multiple queries?

Result should be like this:

maincat   products
melons    0
grapes    2
apples    4

Databases:

database: maincat
id   name
1    apples
2    grapes
3    melons

database: subcat
maincat_id  subcat_id  name
1           1          yellow apples
1           2          green apples
2           3          normal grapes
3           4          watermelons
3           5          honeydew melon

database: products
subcat_id   name
1           yellow apple 1
1           yellow 2
1           yellow 3
2           green apple 1
3           grape 1
3           grape 2

score 2 · Accepted Answer · answered Nov 15 '14 at 08:53

2

select m.name, count(p.subcat_id) as products
from maincat m
left join subcat s on s.maincat_id = m.id
left join products p on p.subcat_id = s.subcat_id
group by m.name

answered Nov 15 '14 at 08:53

juergen d

201,996
37
293
362

thanks alot. when i read about it i thought i would use inner join. both gives the same result though. – Kristian Rafteseth Nov 15 '14 at 08:59
with `inner join`s you don't get maincats where the count is zero. – juergen d Nov 15 '14 at 09:02

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

Yep it is possible. This is a very common case too.

What u need is JOIN on multiple tables.

See this link for a good explanation on how it works and which join u need: https://stackoverflow.com/a/38578/4218046

The explanation there is for 2 tables, but it works the same way for 3 tables.

edited May 23 '17 at 12:27

Community

1
1

answered Nov 15 '14 at 08:29

Fabian S.

2,441
2
24
34

mysql query with info from 3 different tables

2 Answers2