How to connect a field that sometimes has an empty amount to a table

Question

Pasteid Field is a field that is sometimes empty and refers to the post table when it is full, and the post table connects to the user table with the Userid field.

select `posts`.`id` as `pid`, `posts`.`media` as `pmedia`, `posts`.`created_at` as `pcreated_at`, `posts`.`txt`, `users`.`media` as `umedia`, `users`.`id`, `users`.`name` as `name`, `users`.`username`, `posts`.`options`, `posts`.`likes`, `posts`.`comments`, `posts`.`copyid`, `posts`.`pasteid`, `u`.`name` as `replayname`, `u`.`name` as `replayuser` 
from `posts` 
inner join `usersq` on `users`.`id` = `posts`.`userid` 
inner join `posts` as `p` on `posts`.`pasteid` = `p`.`id` 
inner join `users` as `u` on `u`.`id` = `p`.`userid` 
where (`posts`.`block` = 0 and `users`.`view` = 1) and `users`.`status` not in (1, 3) 
order by `posts`.`created_at` desc 
limit 30

In fact, we want to know if the post has been copied, and from which copy it was copied. The problem with the above code is that it only contains the records that the Pasteid field must be filled in and, if empty, does not include the output

post table id txt media pasteid userid 1 eee 1.jpg 1 2 rrr 1 3 eee 1.jpg 1 2

user table id name 1 Ali 2 jack

As you can see, the jack has been copied from behind. Now I need to get Ali's name from the users table and put it in the replayname field

Please add some representative sample data and the desired result. It sounds like you might need OUTER JOIN. — PM 77-1, May 31 '19 at 13:46
Possible duplicate of [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — JeffUK, May 31 '19 at 13:56

George Pant · Answer 1 · 2019-05-31T14:08:55.043

Just use LEFT JOIN instead of INNER JOIN. The LEFT JOIN will return all records from the left table even if they have no match in the right table.

select `posts`.`id` as `pid`, `posts`.`media` as `pmedia`, `posts`.`created_at` as `pcreated_at`, `posts`.`txt`, `users`.`media` as `umedia`, `users`.`id`, `users`.`name` as `name`, `users`.`username`, `posts`.`options`, `posts`.`likes`, `posts`.`comments`, `posts`.`copyid`, `posts`.`pasteid`, `u`.`name` as `replayname`, `u`.`name` as `replayuser` 
from `posts` 
inner join `usersq` on `users`.`id` = `posts`.`userid` 
left join `posts` as `p` on `posts`.`pasteid` = `p`.`id` 
left join `users` as `u` on `u`.`id` = `p`.`userid` 
where (`posts`.`block` = 0 and `users`.`view` = 1) and `users`.`status` not in (1, 3) 
order by `posts`.`created_at` desc 
limit 30

Do not be tired and thankful. But it looks like the code only returns the full pasteid — meysam ghasemloo, May 31 '19 at 13:53
I edited the query try again.If you had an sql fiddle it would be really helpful. — George Pant, May 31 '19 at 14:11

How to connect a field that sometimes has an empty amount to a table

1 Answers1