Trying to get a tricky mysql select statement working - and I need some help to cool off my burning noggin...and I have a feeling one of you MYSQL heroes out there will look at this and reel it off.
Goal: List a given user's songs NOT in a given category.
Three tables: : table 1: song, many fields with assigned UserID, and unique SongID
table 2: category, 3+ fields, with assigned UserID and unique CatID
table 3: linker, one-to-many for listing songs in one or more categories. 3 fields, unique id (index), SongID, CatID
The following gets me close - but does not list a user's songs that aren't assigned to any other category at all OR are a already assigned to a another category (I think thanks to !=).
SELECT DISTINCT song.SongName, song.UserID, song.SongID FROM song
JOIN linker ON song.SongID = linker.SongID
WHERE linker.CatID != 155 AND song.UserID =2
Then, I tried this,
SELECT DISTINCT song.SongName, song.UserID, song.SongID FROM song
LEFT JOIN linker ON song.SongID = linker.SongID
WHERE (linker.SongID IS NULL OR linker.CatID != 155) AND song.UserID =2
Closer but not working (still thanks to != including songs already assigned).
I was thinking I can get away without invoking table 2, since it merely adds and defines categories for a given user. Alternatively, I'm thinking of getting all the user's songs, and then unsetting array values with a given CatID - but this doesn't seem like it should be necessary? I feel like I'm missing something simple? Table structure is not sacred at this point if it absolutely needs to change. Thanks to any who share their thoughts.
Try this (I am used to MSSQL so if my syntax is off, appologies in advance):
SELECT s.SongName, s.UserID, s.SongID
FROM song s
LEFT JOIN linker l on s.SongID = l.SongID AND l.CatID = 155
WHERE s.UserID = 2
AND l.ID is null
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments