CODEWITHSUNDEEP
mysqlsql
enchance
enchance

Reputation: 30411

MySQL join while counting on joined tables

When you have a table which has relationships with 2 others through FKs, is it possible to query the main table while only counting how many times its ID exists in the other 2?

For example:

    id  title    product_id  contact_id
------  -------  ----------  ----------
    11  Mug             110          33
    11  Mug             110         297
    11  Mug             378          33
    11  Mug             378         297
    12  Monster         299          75
    12  Monster         299         291
    12  Monster         299         296

My query which results in above:

SELECT t1.id, t1.title, t2.product_id, t3.contact_id FROM t1
    JOIN t2 ON t1.id = t2.rfq_id
    JOIN t3 ON t1.id = t3.rfq_id

I'm only interested in the COUNT():

    id  title    product_count  contact_count
------  -------  -------------  -------------
    11  Mug                  2              2
    12  Monster              1              3

Upvotes: 0

Views: 43

Answers (3)

Venkatesh Panabaka
Venkatesh Panabaka

Reputation: 2154

Try these

  SELECT t1.id, t1.title, 
         count(distinct t2.product_id) as product_id, 
         count(distinct  t3.contact_id) as contact_id FROM t1
  JOIN t2 ON t1.id = t2.rfq_id
  JOIN t3 ON t1.id = t3.rfq_id
  group by t1.id, t1.title

Thank you.

Upvotes: 1

potashin
potashin

Reputation: 44581

You can try with group by and count only distinct values:

select t1.id
     , t1.title
     , count(distinct t2.product_id) as product_count 
     , count(distinct t3.contact_id) as contact_count
from t1
join t2 on t1.id = t2.rfq_id
join t3 on t1.id = t3.rfq_id
group by t1.id
       , t1.title

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can do something like:

SELECT t1.id, t1.title, 
       COUNT(DISTINCT t2.product_id) AS product_count, 
       COUNT(DISTINCT t3.contact_id) AS contract_count 
FROM t1
JOIN t2 ON t1.id = t2.rfq_id
JOIN t3 ON t1.id = t3.rfq_id
GROUP BY t1.id, t1.title

Upvotes: 0

Related Questions