CODEWITHSUNDEEP
mysqlsql
Peter
Peter

Reputation: 1931

Mysql left join with limit returning join record for one row

How do i join a table using limit? I have the below query but it doesn't work as expected. Am using left join to select only one row from table, but it only select one record as expected for the first row while it returns null on others Even when they have file saved in TABLE_USER_FILES.

TABLE_USERS

uid | u_name  
----|---------
p1  | Peter 
j1  | John
f1  | Foo
b1  | Bar

TABLE_USER_POST

pid | p_name  | p_uid
----|---------|--------
xp1 | PHP     | p1
xp2 | SQL     | p1
xp3 | JS      | j1
xp4 | CSS     | b1

TABLE_USER_FILES

fid | f_uid  | f_url   | f_path
----|--------|---------|----------
fa1 | p1     | ax.png  | gallery
fb2 | p1     | bc.png  | gallery
bc3 | j1     | cc.png  | gallery
fd4 | f1     | cx.png  | gallery
fe5 | j1     | qd.png  | gallery

Query

SELECT post.*, user.u_name, files.f_url
FROM TABLE_USER_POST post

INNER JOIN TABLE_USERS user
ON user.uid = post.p_uid

LEFT JOIN (
    SELECT f_url, f_uid
        FROM TABLE_USER_FILES
        WHERE f_path = "gallery"
        ORDER BY fid DESC
    LIMIT 1
) files
ON files.f_uid = post.p_uid

ORDER BY post.pid DESC 
LIMIT 0, 20

Expected result

pid | p_name  | p_uid  | u_name  | f_url
----|---------|--------|---------|---------
xp1 | PHP     | p1     | Peter   | bc.png
xp2 | SQL     | p1     | Peter   | bc.png
xp3 | JS      | j1     | John    | qd.png
xp4 | CSS     | b1     | Bar     | NULL

Upvotes: 0

Views: 114

Answers (2)

Prasuna
Prasuna

Reputation: 49

Please try this instead.

SELECT post.*, user.u_name, files.f_url 
  FROM TABLE_USER_POSTS post 
  LEFT JOIN TABLE_USER_FILES files 
       ON files.f_uid = post.p_uid 
       AND files.fid = (SELECT MAX(fid) FROM TABLE_USER_FILES WHERE f_uid = files.f_uid) 
 INNER JOIN TABLE_USERS user 
       ON user.uid = post.p_uid 
 ORDER BY post.pid DESC;

Thank you!

Upvotes: 0

id'7238
id'7238

Reputation: 2593

There are many solutions here. For example, LATERAL in MySQL 8.0.14+

SELECT post.*, user.u_name, files.f_url
FROM TABLE_USER_POST post

INNER JOIN TABLE_USERS user
ON user.uid = post.p_uid

LEFT OUTER JOIN LATERAL (
    SELECT f_url, f_uid
    FROM TABLE_USER_FILES tuf
    WHERE f_path = "gallery"
      AND tuf.f_uid = post.p_uid
    ORDER BY fid DESC
    LIMIT 1
) files ON true

ORDER BY post.pid DESC 
LIMIT 0, 20

If only one column from TABLE_USER_FILES is needed, then the query in the SELECT clause:

SELECT post.*, user.u_name,
  ( SELECT f_url
    FROM TABLE_USER_FILES tuf
    WHERE f_path = "gallery"
      AND tuf.f_uid = post.p_uid
    ORDER BY fid DESC
    LIMIT 1
  ) AS f_url
FROM TABLE_USER_POST post

INNER JOIN TABLE_USERS user
ON user.uid = post.p_uid

ORDER BY post.pid DESC 
LIMIT 0, 20

db<>fiddle

Upvotes: 1

Related Questions