CODEWITHSUNDEEP
mysqljoin
atromgame
atromgame

Reputation: 442

Right outer join in MySQL

I have two tables: Users and Files.

Users table has 10 rows, Files table has 450 rows which has a column for user ID's (UserID).

I want to query list of all People and files they last downloaded. Files table has LastDownloadTime field which is of type DateTime.

This is my SQL query;

Select
    Users.Name , Files.Name
FROM Users
RIGHT OUTER JOIN Files on Users.ID = (
    Select Files.UserID
    FROM Files
    ORDER BY LastDownloadTime DESC Limit 1
)

Above query returns all Users and Files. What should be the right query for this?

Upvotes: 0

Views: 2887

Answers (3)

Harold Sota
Harold Sota

Reputation: 7566

See these: 

 SELECT Users.Name , Files.Name 
    FROM Users 
    RIGHT OUTER JOIN
    ( SELECT Files.Name 
      FROM Files 
      INNER JOIN
          ( SELECT TOP 1 Files.UserID ,Max(LastDownloadTime ) as LastDownloadTime 
                    FROM Files 
                    ORDER BY LastDownloadTime DESC,Files.UserID  ) as f2
  ON Files.UserID  = f2.UserID  AND Files.LastDownloadTime  = f2.LastDownloadTime 
   ) as f  ON Users.ID = f.UserID

Upvotes: 1

alex
alex

Reputation: 3720

You could do this:

Select
    u.Name,
    (Select f.Name from Files f where u.ID = f.ID order by LastDownloadDate LIMIT 1)
FROM Users u

Upvotes: 2

Frank Heikens
Frank Heikens

Reputation: 127096

Are you sure about the RIGHT JOIN? Your question seems different.

Select 
    Users.Name 
    ,f2.Name 
FROM 
    Users  
        JOIN (SELECT userid, MAX(lastdownloadtime) AS latest FROM Files GROUP BY userid) f1 ON f1.userid = Users.id
        JOIN Files f2 ON (Users.ID = f2.userid AND f2.lastdownloadtime = f1.latest);

Upvotes: 0

Related Questions