Can I JOIN a table based on an SQL if-statement?

Question

I have three tables being used for this problem: songs, blacklist, and whitelist. The songs table has a column named "accessType" which stores one of these four values: public, private, blacklist, whitelist. I'm trying to fetch a list of all the songs a user may access. The first condition is that songs.accessType != private. Here comes the tricky part: if songs.accessType = blacklist, I need to check that the user's ID is not within the blacklist table. Similarly, if songs.accessType = whitelist, I need to check that the user's ID is within the whitelist table. It seems to me like I need to JOIN the blacklist and/or whitelist table under certain conditions, but I have no idea if this is possible, or even the right approach. Any help is much appreciated, thanks!

Here's an explanation of my table schema:

songs: id, name, acessType, userID

blacklist: songID, userID

whitelist: songID, userID

Edit
Here's a breakdown of the foreign keys:
songs.id -> blacklist.songID
songs.id -> whitelist.songID
songs.userID -> blacklist.userID
songs.userID -> whitelist.userID

Ravish's answer is working, but I'm interested to see why people are saying it's not efficient as possible.

Also, what is the significance of the @ symbol before userID? I replaced @userID with ? and I am binding parameters to the query.

@Workshop Alex - private songs should not be included at all.

Answer 1

You try joining BOTH tables with two conditions for each (userID and accessType).

Answer 2

SELECT
    s.*
FROM
    songs s
LEFT JOIN
    whitelist wl ON s.songid = wl.songid AND wl.userid = @userid
LEFT JOIN
    blacklist bl ON s.songid = bl.songid AND bl.userid = @userid
WHERE
    s.userid = @userid
AND
    s.accessType != 'private'
AND
    (
     s.accessType = 'public'
     OR
     (s.accessType = 'whitelist' AND wl.songid IS NOT NULL)
     OR
     (s.accessType = 'blacklist' AND bl.songid IS NULL)  
    )

Answer 3

use string comparison in your ON clause and intelligently join with your black and whitelist

    SELECT `s`.*
      FROM `songs` `s`
INNER JOIN `whitelist` `w`
        ON `s`.`id` = `w`.`songID`
       AND `s`.`userID` = `w`.`userID`
       AND `s`.`accessType` = 'whitelist';
 LEFT JOIN `blacklist` `b`
        ON `s`.`id` = `b`.`songID`
       AND `s`.`userID` = `b`.`userID`
       AND `s`.`accessType` = 'blacklist'
     WHERE `b`.`songID` IS NULL
       AND `s`.`accessType` <> 'private'
       AND `s`.`userID` = $youruserid;

this will select all songs for a given userid which are public, inner joined with whitelist (so you only get songs on the whitelist).

the left join with blacklists then selects only rows which don’t have a blacklist entry

ps. i’m not exactly sure what your foreign key relations are, maybe you could elaborate that a little?

Answer 4

It generally helps to try to write down your query in plain english (or whatever your native language is.)

You need several selections:
1) All public songs
2) All Whitelist songs where the user-ID is in the whitelist
3) All blacklisted songs where the user-ID is NOT in the blacklist.
4) All private songs of the user himself.

Thus, four conditions, which translates to:

SELECT 
    *
FROM 
    songs
WHERE 
    (acessType = 'Public') 
OR 
    (acessType = 'Whitelist' AND id IN (
        SELECT songID FROM WhiteList WHERE userID = :userID) 
    ) 
OR 
    (acessType = 'Blacklist' AND id NOT IN (
        SELECT songID FROM BlackList WHERE userID = :userID) 
    )

(Btw, I've copied your spelling error too!)

Simple and effective. And let me now explain why this works. In the Where clause, I do the four subselections. Since I want the resultset from a single table, I don't need a join. Don't need an union either since the conditions are less complex than I expected. (Although it helps to split the query in different subqueries first.)

The first condition checks for private songs that are owned by the user. You might just skip the "acessType = 'Private' AND" part there so you can select all songs owned by user, no matter if he blacklisted himself or not. If you don't want private songs to be included, then skip this condition!

The second condition adds all the public songs. That one has no additional restrictions.

For the third condition I need to select a list of songs first that the user can access. Thus a sub-select is added and I check for ID's in this list.

For the fourth condition, I need all song ID's that are NOT in the blacklist for the specific user. Again, a sub-select but this time, ID should not be in the list.

Combined, the four conditions will return a list of songs record that qualify to your wishes. Plain and simple. Don't try to make things more complex by thinking about joins, when all you do is selecting data from a single table. Sometimes, a UNION might be required but a JOIN is needed to combine myultiple tables together into a single result. You don't want that!

Btw, the :userID translates to the parameter part of the query. This tends to be a bit specific to the SQL implementation and sometimes ? or something else is used. Just read "The user ID parameter" when you see :userID.

Answer 5

SELECT * FROM Songs s WHERE (s.AccessType='BlackList' AND @UserId Not in(SELECT UserId From BlackList WHERE SongId=s.Id)) OR (s.AccessType='WhiteList' AND @UserId  in(SELECT UserId From WhiteList WHERE SongId=s.Id))

Answer 6

SELECT * FROM SONGS s
WHERE
   accesstype='public'
   OR (accesstype='whitelist' 
      AND EXISTS (SELECT null FROM WHITELIST wl WHERE 
          wl.songid = s.id AND wl.userid=s.userid))
   OR (accesstype='blacklist' 
      AND NOT EXISTS (SELECT null FROM BLACKLIST bl WHERE 
          bl.songid = s.id AND bl.userid= s.userid))

If accesstype=private, it won't slip through, there's no need for an additional clause.

I chose using correlated subqueries because they are, in this case, more readable than filtered left joins. Perhaps MySQL is a tiny bit less efficient at them, but one should not make decisions about design based on minor temporary limitations of the query optimizer, unless the performance difference is absolutely critical. Perhaps the next version of MySQL will be even faster with this than a left join.

Answer 7

There is a bit of confusion about this question! A few answers assume you have selected a userID somewhere and want to select all songs for this specific user. Others have assumed that you just wanted a list of all users where (userID, songID) is in the whitelist but not in the blacklist. As a result, there are two possible answers possible to this Q!

So, it could be read as two different questions:
Question 1: Given a specific user, which non-private songs can he access that are public, whitelisted or not blacklisted?
Question 2: Which non-private songs from which users are whitelisted or public or not blacklisted?

Both Q's have been answered. Question 2 was answered by richardtallent and the other by me. In Q2, using EXISTS is the best option simply because the sub-select is always returning a single result and EXISTS will just translate to true or false, which is more optimized.
In Q1, the use of IN is better because you need to check for multiple values in the same subset. The parser will execute the sub-selection once and store the resultset, instead of executing this for every row. Thus, in this case the use of IN is better.

I hope this will clear up some of the confusion that has occurred in this Q. Technically speaking, the outcome of the answer could be considered subjective! The confusion arises from the line "I'm trying to fetch a list of all the songs a user may access.". This made me and several others think that you've already selected a single user and needed to collect his songs.