how to fetch songs based on multiple conditions from joined tables

user_14808730 0
Viewed 48

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.

subquery exists join mysql sql
3 Answers

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))
user_104988280

Two things.

  1. Use parentheses to group WHERE clauses; otherwise they evaluate left-to-right.

  2. Use LEFT JOIN to avoid losing items from your first table that don't match any items in your second table.

This should work (https://www.db-fiddle.com/f/6dAz91ejhe8AbGECFDihbu/0)

SELECT id,available_for,song_name 
  FROM songs
  LEFT JOIN song_clubs ON songs.id = song_clubs.song_id
 WHERE songs.status = 1
   AND (song_clubs.club_id=1 or songs.available_for ='all')
 ORDER BY id;
user_2056080

you can use this answer too

select unique id,available_for,song from songs,song_clubs
WHERE (song_clubs.song_id = songs.id and songs.status = 1 and song_clubs.club_id=1) or (songs.available_for ='all');

Here I use full join to select all the matches and then select the unique id values for the songs so you can get only the required 2 rows

enter image description here

Note: It is not the best performance query if you have huge tables. and it is better to use EXISTS or LEFT JOIN.so other answers are more better for performance and this answer is just another way to do that.

user_151533260
Related