0

Hi I'm very new to Stack Overflow so apologies. So currently, I have 2 tables, "albums" and "bands" which have values inside it already.

I want to return only the 'name' column of bands (aka name of the bands) with no albums. So I did the following:-

SELECT bands.name AS 'Band Name'
FROM bands
LEFT JOIN albums ON bands.id = albums.band_id
GROUP BY albums.band_id
HAVING COUNT(albums.id) = 0;

Buttt, I kept on getting this error:

Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'record_company.bands.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

So after some digging around, it kinda worked but it returned 2 columns, when I only want the one 'name' column. Here's the query that I used:

SELECT b.name as 'Band Name', a.band_id
FROM bands b
LEFT JOIN albums a ON b.id = a.band_id
GROUP BY a.band_id, b.name
HAVING COUNT(a.band_id) = 0;

And the results: Result of second query

I realized that if I were to use group by, I'd need to group it based on the column selected, otherwise it wont work. I guess my question is is it possible to select A and group by B. Or does it always have to be Select A and group by A. And how do I return that one column only?

Many thanks in advance!

Barmar
  • 741,623
  • 53
  • 500
  • 612
user21719055
  • 1
  • Use `GROUP BY b.id`. You shouldn't group by a column in the `LEFT JOIN`ed table, since all the rows with no match will be in the same group. – Barmar Apr 24 '23 at 05:13
  • This is not the best way to get all the bands with no albumbs. See the linked question for better ways. – Barmar Apr 24 '23 at 05:15
  • You should group by `bands.name`, and you should have a unique constraint on that name. So if you have two bands with the same name, you'l have to make them distinct, so f there is another band calling themself Beatles, you may have 'Beatles' and ' Beatles (local band in Massachusetts)'. If you want to allow duplicate names, you could group by `bands.id`, but then there might suddenly be 'Beatles' in your result, s you'd want to add another column such as a description containing 'local band in Massachusetts'. Grouping by the id suffices to select various columns of the table. – Thorsten Kettner Apr 24 '23 at 05:29
  • But yes, `NOT IN` or `NOT EXISTS` would be much more natural here, and would prefer them over an anti join. – Thorsten Kettner Apr 24 '23 at 05:29
  • It should be `AS "Band Name"` by the way, not `AS 'Band Name'`. Single quotes are for string literals, double quotes for names in SQL. (The DBMS should raise a syntax error hence, but MySQL is trying to be nice with you here.) – Thorsten Kettner Apr 24 '23 at 05:32

0 Answers0