MySQL

[jojohilvan24]

[pranabroygithub]

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

In the last line instead of albums.id there should be albums.band_id

so the code should be

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

[lzyslh]

Yes, you are right. But why should we use GROUP BY in this situation, wouldn't a simple WHERE return the same result. Something like this:

SELECT bands.name AS 'Band Name' FROM bands LEFT JOIN albums ON bands.id = albums.band_id WHERE albums.band_id IS NULL

@pranabroygithub What do you think?

[lingyutan]

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

In the last line instead of albums.id there should be albums.band_id

so the code should be

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

I don't think there is any difference between them as 'id' and 'band_id' will both be NULL after a LEFT JOIN if a band does not have any albums.

[vinaykumar4312]

In Sql select bands.name as 'Band Name' from bands left join albums on bands.id = albums.band_id group by albums.id having count(albums.band_id) = 0; for me getting error Column 'bands.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. anyone give solution plz

[Rodovs]

In Sql select bands.name as 'Band Name' from bands left join albums on bands.id = albums.band_id group by albums.id having count(albums.band_id) = 0; for me getting error Column 'bands.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. anyone give solution plz

add the bands.name to the group by, like this:

select bands.name as 'Band Name' from bands left join albums on bands.id = albums.band_id group by albums.id, bands.name having count(albums.band_id) = 0;