how to fetch songs based on multiple conditions from joined tables

Question

I have two tables songs and song_clubs. The schema is below:-

songs schema
 id        available_for      song_name     status
 1           all                 Song 1      1
 2          selection            Song 2      1
 3          selection            Song 3      1
song_clubs schema
song_id     club_id
 2            1
 2            2
 3            2

Now i want to fetch the songs of club id 1 and the song is available for all clubs. My execpted output is like below:-

id        available_for      song_name
 1           all                 Song 1
 2          selection            Song 2

I have tried below Query

select id,available_for,song_name from songs 
 JOIN 
song_clubs
on song_clubs.song_id = songs.id
WHERE songs.status =1 and song_clubs.club_id=1 or songs.available_for ='all'

But its only returning one entry that is selection based.

Answer 1

You can do it with EXISTS:

SELECT s.id, s.available_for, s.song_name 
FROM songs s
WHERE s.status =1 AND (  
      s.available_for = 'all' 
   OR EXISTS (SELECT 1 FROM song_clubs c WHERE c.club_id = 1 AND c.song_id = s.id))

or with the operator IN:

SELECT id, available_for, song_name 
FROM songs 
WHERE status =1 AND (
      available_for = 'all' 
   OR id IN (SELECT song_id FROM song_clubs WHERE club_id = 1))