CODEWITHSUNDEEP
mysqlsql
Lukesoft
Lukesoft

Reputation: 953

Inner Join 2 tables and count occurrences of foreign key in each

I am using Mysql and I have 3 tables,

banners (id,type,page)

clicks (id,banner_id)

impressions (id,banner_id)

Where banner_id is the foreign key in clicks and impressions tables. I need to produce rows like this

Required Result Table

id, type, clicks_count, impressions_count

Perfomance-wise, what is the best mysql query to use to achieve the result above.

Upvotes: 0

Views: 850

Answers (1)

jarlh
jarlh

Reputation: 44766

Simply JOIN the three tables, use COUNT() and GROUP BY:

select id, type, count(c.id) clicks_count, count(i.id) as impressions_count
from banners b
  left join clicks c on b.id = c.banner_id
  left join impressions i on b.id = i.banner_id
group by id, type

Or use correlated sub-queries to count:

select id, type,
       (select count(*) from clicks c where b.id = c.banner_id) clicks_count,
       (select count(*) from impressions i where b.id = i.banner_id) impressions_count
from banners b

Upvotes: 1

Related Questions