CODEWITHSUNDEEP
mysql
brika28
brika28

Reputation: 65

Counting results from 2 tables base on a third table

I am trying to count number of questions and answers for every company but I want to count them in a single query.

So the problem is this: table pitanje (question) is linked with tvrtka (company) pitanje.tvrtka_id=tvrtka.tvrtka_id and table answer (odgovor) is linked with question table odgovor.pitanje_id = pitanje.pitanje_id

I have tried with something similar to this and got some faulty results (I counted question and answers separately for testing).

SELECT tvrtka.naziv,
(SELECT COUNT(*) FROM pitanje WHERE tvrtka.tvrtka_id = pitanje.tvrtka_id) AS brojPitanja,
(SELECT COUNT(*) FROM odgovor WHERE odgovor.pitanje_id = pitanje.pitanje_id) AS brojOdgovora
FROM tvrtka
ORDER BY tvrtka.tvrtka_id

But all I get is some MySQL errors (unknown column, not uniqe alias etc.) now and can't get even to a faulty results.

If someone can just explain me the concept of the code above. A lot of answers are written like this but I can't figure out how to do it on my example.

Upvotes: 1

Views: 35

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31812

With englisch names it would be something like this:

SELECT 
    c.name,
    COUNT(DISTINCT q.question_id) as numQuestions,
    COUNT(a.answer_id) as numAnswers
FROM company c
LEFT JOIN questions q ON q.company_id = c.company_id
LEFT JOIN answers a ON a.question_id = q.question_id
GROUP BY c.name

I let it to you, to translate the table and column names back to your schema.

What is wrong with my code?

Here: 

(SELECT COUNT(*) FROM pitanje WHERE tvrtka.tvrtka_id = pitanje.tvrtka_id) AS brojPitanja,
(SELECT COUNT(*) FROM odgovor WHERE odgovor.pitanje_id = pitanje.pitanje_id) AS brojOdgovora

in your second subquery with WHERE odgovor.pitanje_id = pitanje.pitanje_id you are trying to reference a table (pitanje) from another subquery. This is not possible. You can only reference a table in the FROM clause of the outer query, which is tvrtka.

Upvotes: 1

Related Questions