"Where" statement based on result of a join

user_8901194 0
Viewed 41

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!

mysql sql
4 Answers

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

user_68918940

If I understand you correctly - you want a left join but only when it's empty, so you are looking for

SELECT songs.name,
       COUNT(performances.songid) as performances
FROM table_songs songs
LEFT JOIN table_songperformances performances 
               on songs.id = performances.songid
WHERE performaces.songid IS NULL
GROUP BY songs.id DESC  
ORDER BY songs.id DESC
user_26557770

The most efficient method is to use NOT EXISTS:

select s.name,
from table_songs s
where not exists (select 1
                  from table_songperformances p
                  where s.id = p.songid
                 );

This is much, much more efficient than the method that does a left join, aggregates all the data, and then filters out the songs that have no performances.

user_11440350

Not an expert in SQL either, but checking equality in SQL is with '=' not '=='. Maybe the problem is somewhere else in the code, but try that.

user_142756540
Related