CODEWITHSUNDEEP
mysql
Kristian Rafteseth
Kristian Rafteseth

Reputation: 2032

mysql query with info from 3 different tables

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

Upvotes: 0

Views: 27

Answers (2)

juergen d
juergen d

Reputation: 204746

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

Upvotes: 2

Fabian S.
Fabian S.

Reputation: 2529

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.

Upvotes: 0

Related Questions