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
CodePudding user response：
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!
CodePudding user response：
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