join and count: group by issue when trying to perform a left join only

user_2399035 0
Viewed 19

I have two tables:

TABLE A (fields)
ID_A | NAME_A
_____________
  1A
  2A
  3A

TABLE B (forms)
ID_B | NAME_B
____________
  1B
  2B
  3B
  4B

Then I mantain a table where I set the link between A and B

TABLE C (form_row)
ID_C | ID_B | ID_A
___________________
  1      1B    2A
  2      1B    3A
  3      2B    3A

I want to get the list of all the items in table A and the count of how many times they have been used in table C

If I use this query:

SELECT count(form_row.id_form) as volte, campi.* 
FROM campi JOIN form_row ON campi.id=form_row.id_form 
WHERE utente=1 
GROUP BY form_row.id_form

It will give me all those fields of table A that have a match in table C but will exclude those that are not in table C. So for example 1A will not be included. Results:

2A 1 time
3A 2 times

So I tried to switch to a left join like this:

SELECT count(form_row.id_form) as volte, campi.* 
FROM campi LEFT JOIN form_row ON campi.id=form_row.id_form 
WHERE utente=1 
GROUP BY form_row.id_form

but this query will raise the following error:

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'campi.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

How to fix? My expected result would be to have:

1A 0 times
2A 1 time
3A 2 times

I have tried to understand exactly what is the point of this message but I don't manage to get a clear picture of this issue and thus how to solve it

mysql
1 Answers

First, you need a query that aggregates in TableC:

SELECT ID_A, COUNT(*) counter
FROM TableC
GROUP BY ID_A

and use LEFT join of TableA to that query:

SELECT a.*, COALESCE(c.counter, 0) counter
FROM TableA a 
LEFT JOIN (
  SELECT ID_A, COUNT(*) counter
  FROM TableC
  GROUP BY ID_A
) c ON c.ID_A = a.ID_A
user_104988280
Related