CODEWITHSUNDEEP
mysqlsql
Icaval
Icaval

Reputation: 29

"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!

Upvotes: 2

Views: 43

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 1

LongChalk_Rotem_Meron
LongChalk_Rotem_Meron

Reputation: 803

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

Upvotes: 1

Ashutosh Thakur
Ashutosh Thakur

Reputation: 94

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

Upvotes: 3

AndrejKostov
AndrejKostov

Reputation: 31

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.

Upvotes: -2

Related Questions