“Where” statement based on result of a join

When I run this:

select songs.name,
count(performances.songid) as performances
from table_songs songs
left join table_songperformances performances on songs.id = performances.songid
group by songs.id desc  
ORDER BY `songs`.`id`  DESC

I get some results where the number of performances is equal to zero. I want to change my query to only return those results, but “where performances == 0” doesn’t work.

I know this is simple, but I haven’t been able to figure it out. Thanks for any help!

Answer

The having clause will help you get the correct results whenever you need to put a condition on an aggregated column.

Try this:-

    select songs.name,
count(performances.songid) as performances
from table_songs songs
left join table_songperformances performances on songs.id = performances.songid
group by songs.id desc having count(performances.songid)=0
ORDER BY `songs`.`id`  DESC

Oracle Doc : https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj14854.html

Leave a Reply

Your email address will not be published. Required fields are marked *