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)