Query results are multiplied by 2

Question

3 tables. app_forms, app_results, app_exhibitions

I want to get the form ID, form name, the count of the results and the exhibitions linked to the form. At the moment I got this query:

SELECT 
f.id as id, 
f.name as formname, 
count(res.form_id) as resultcount, 
group_concat(b.name separator ', ') as exhibitionname 
FROM app_forms f 
LEFT JOIN app_results res ON f.id = res.form_id 
LEFT JOIN app_exhibitions exh ON exh.form_id = f.id 
GROUP by f.id

Problem is, it multiply's things by 2. Because I got 4 exhbitions grouped in the concat group (instead of 2) and I got 4 results instead of 2 in the count group. I'm stuck in how to solve this, it has probably something to do with the doube left join.

Answer 1

The simplest solution is to use DISTINCT on the count, if you have a primary key on your app_results table for example app_results.id:

SELECT 
  f.id as id, 
  f.name as formname, 
  count(DISTINCT res.id) as resultcount, 
  group_concat(b.name separator ', ') as exhibitionname 
FROM
  app_forms f LEFT JOIN app_results res ON f.id = res.form_id 
  LEFT JOIN app_exhibitions exh ON exh.form_id = f.id 
GROUP by f.id

or to join an already grouped table (i suppose that b.name is exh.name):

SELECT 
  f.id as id, 
  f.name as formname, 
  count(res.form_id) as resultcount, 
  exh.exhibitionname 
FROM
  app_forms f LEFT JOIN app_results res ON f.id = res.form_id 
  LEFT JOIN (
    SELECT form_id, group_concat(name separator ', ') as exhibitionname
    FROM app_exhibitions
    GROUP BY form_id
  ) exh ON exh.form_id = f.id 
GROUP by f.id