MySql - join troubles
I have two tables : tracklist(trackid artist event date)
and radded(trackid, user)
with relation 1 to n (trackid are the keys on each table).
I would like to return the whole tracklist table, by adding a new field called exist
that should be 1 or 0 if a user (passed trought the query) exist on the radded table for the related trackid for each row.
Example :
tracklist
*trackid artist event date*
123 art1 eve1 date1
321 art2 eve2 date2
456 art3 eve3 date3
298 art4 eve4 date4
565 art5 eve5 date5
radded
trackid user
123 luigi
123 mari开发者_如何学Goo
321 luigi
298 mario
298 antonio
if i use the luigi as user, the output must be :
123 art1 eve1 date1 1
321 art2 eve2 date2 1
456 art3 eve3 date3 0
298 art4 eve4 date4 0
565 art5 eve5 date5 0
How can I do this query?
Use a LEFT OUTER JOIN
from tracklist
to radded where user="luigi"
. This will get each line from tracklist
exactly one time, and will put the username
if it exists, or NULL
if no appropriate username was found.
Then convert the username to 1
, or the NULL
to 0
and rename the column as exist
.
SELECT t.trackid, t.artist, t.event, t.date, IF(r.user IS NULL, 0, 1) AS exist
FROM tracklist t
LEFT OUTER JOIN (SELECT * from radded where user = "luigi") r
ON t.trackid = r.trackid
SELECT
t.trackid,
t.artist,
t.event,
t.date,
MAX(CASE r.user WHEN 'luigi' THEN 1 ELSE 0 END) AS flag
FROM tracklist t
LEFT JOIN radded r ON t.trackid = r.trackid
GROUP BY
t.trackid,
t.artist,
t.event,
t.date
EDIT: some explanation on how it works.
Here's a similar script that doesn't use grouping:
SELECT
t.trackid,
t.artist,
t.event,
t.date,
CASE r.user WHEN 'luigi' THEN 1 ELSE 0 END AS flag
FROM tracklist t
LEFT JOIN radded r ON t.trackid = r.trackid
Its output would be:
trackid artist event date flag
------- ------ ----- ---- ----
123 art1 eve1 date1 1
123 art1 eve1 date1 0
321 art2 eve2 date2 1
456 art3 eve3 date3 0
298 art4 eve4 date4 0
298 art4 eve4 date4 0
565 art5 eve5 date5 0
The trackids
that are repeated in radded
, are repeated in this list accordingly. To get rid of duplicates we use GROUP BY. MAX
is used to get the correct value for flag
.
try this:
SELECT tl.trackid, artist, event, date,
(r.user is not null) as exists
FROM tracklist tl
LEFT JOIN radded r
ON tl.trackid = r.trackid
AND r.user = 'luigi'
result should be:
123 art1 eve1 date1 1
321 art1 eve1 date1 1
456 art1 eve1 date1 0
298 art1 eve1 date1 0
565 art1 eve1 date1 0
(edited to only get a single row for each track)
also make sure to have an index covering radded(trackid, user)
, radded(user)
, tracklist(trackid)
and radded(trackid)
精彩评论