CODEWITHSUNDEEP
mysqljoincount
Ahmed-Anas
Ahmed-Anas

Reputation: 5619

query forumaltion with multiple joins and multiple count for each table

hey guys was hoping you could help me out.

I am trying to make a query such that I have one main table with a foreign key in three different tables. Now I want to count its entries in all 3 tables seperately. This is what I have so far and hope you guys can help me fix it.

SELECT box_code.id, count( box_code_unused.id ) AS total, count( box_code_used.id ) AS total2, count( box_code_expired.id ) AS total3
FROM box_code
JOIN box_code_used ON box_code_used.box_code_id = box_code.id
JOIN box_code_unused ON box_code_unused.box_code_id = box_code.id
JOIN box_code_expired ON box_code_expired .box_code_id = box_code.id
GROUP BY box_code.id

This approach would work if I was counting entries from only one table, but since I am counting from 3 tables its incorrect.

Thanks in advance.

Upvotes: 1

Views: 74

Answers (3)

Ankur
Ankur

Reputation: 12774

I think you can use right join. that way it will include records not available in one table but available in other.

Upvotes: 0

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

Use the subqueries like this:

SELECT box_code.id, 
    (SELECT COUNT(*) FROM box_code_used b1 WHERE b1.box_code_id = box_code.id) AS total1,
    (SELECT COUNT(*) FROM box_code_unused b2 WHERE b2.box_code_id = box_code.id) AS total2,
    (SELECT COUNT(*) FROM box_code_expired b3 WHERE b3.box_code_id = box_code.id) AS total3
FROM box_code

Upvotes: 1

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

You might count distinct occurrences of ID:

SELECT box_code.id, 
       count(distinct box_code_unused.id ) AS total, 
       count(distinct box_code_used.id ) AS total2, 
       count(distinct box_code_expired.id ) AS total3
FROM box_code
LEFT JOIN box_code_used ON box_code_used.box_code_id = box_code.id
LEFT JOIN box_code_unused ON box_code_unused.box_code_id = box_code.id
LEFT JOIN box_code_expired ON box_code_expired .box_code_id = box_code.id
GROUP BY box_code.id

Upvotes: 1

Related Questions