Php mysql left join where clause error?

A post may have comments or not. Use LEFT JOIN instead of INNER JOIN A post may have replies or not. Use LEFT JOIN instead of INNER JOIN in that join too When LEFT JOIN is used, a condition like WHERE comments.

Deleted = 0 that includes a field (from the right table ( comments ) in the LEFT JOIN), the LEFT JOIN is cancelled. So, we should put this condition in the ON clause and not in the WHERE SELECT * FROM posts p LEFT JOIN comments c ON p. Post_id = c.

Post_id AND c. Deleted = 0 LEFT JOIN replies r ON p. Post_id = r.

Post_id AND r. Deleted = 0 WHERE p. User_id = $user_id Thinking more clearly, the above will show what the question describes but in cases with say, 4 comments and 3 replies, 12 rows will be returned (3x4).

Which is probably not wanted. The following 2nd try does not have such issue I don't see a post. Text or comment.

Text or reply. Text in the tables but anyway, you'll get the idea. You can remove the 3 text lines if not appropriate ( SELECT p.

Post_id AS post_id , 0 AS type , p. Post_id AS id , p. Text AS text FROM posts p WHERE p.

User_id = $user_id ) UNION ALL ( SELECT p. Post_id AS post_id , 1 AS type , c. Comment_id AS id , c.

Text AS text FROM posts p JOIN comments c ON p. Post_id = c. Post_id WHERE p.

User_id = $user_id AND c. Deleted = 0 ) UNION ALL ( SELECT p. Post_id AS post_id , 2 AS type , r.

Reply_id AS id , r. Text AS text FROM posts p JOIN replies r ON p. Post_id = r.

Post_id WHERE p. User_id = $user_id AND r. Deleted = 0 ) ORDER BY post_id , post_type , id The 0,1,2 stand for post, comment, reply.

A post may have comments or not. Use LEFT JOIN instead of INNER JOIN. A post may have replies or not.

Use LEFT JOIN instead of INNER JOIN in that join too. When LEFT JOIN is used, a condition like WHERE comments. Deleted = 0 that includes a field (from the right table (comments) in the LEFT JOIN), the LEFT JOIN is cancelled.So, we should put this condition in the ON clause and not in the WHERE.

SELECT * FROM posts p LEFT JOIN comments c ON p. Post_id = c. Post_id AND c.

Deleted = 0 LEFT JOIN replies r ON p. Post_id = r. Post_id AND r.

Deleted = 0 WHERE p. User_id = $user_id Thinking more clearly, the above will show what the question describes but in cases with say, 4 comments and 3 replies, 12 rows will be returned (3x4). Which is probably not wanted.

The following 2nd try does not have such issue. I don't see a post. Text or comment.

Text or reply. Text in the tables but anyway, you'll get the idea. You can remove the 3 text lines if not appropriate.

( SELECT p. Post_id AS post_id , 0 AS type , p. Post_id AS id , p.

Text AS text FROM posts p WHERE p. User_id = $user_id ) UNION ALL ( SELECT p. Post_id AS post_id , 1 AS type , c.

Comment_id AS id , c. Text AS text FROM posts p JOIN comments c ON p. Post_id = c.

Post_id WHERE p. User_id = $user_id AND c. Deleted = 0 ) UNION ALL ( SELECT p.

Post_id AS post_id , 2 AS type , r. Reply_id AS id , r. Text AS text FROM posts p JOIN replies r ON p.

Post_id = r. Post_id WHERE p. User_id = $user_id AND r.

Deleted = 0 ) ORDER BY post_id , post_type , id The 0,1,2 stand for post, comment, reply.

Edited, I am actually using LEFT. – fxuser Jun 4 at 22:41 I tried the select query but it still counts the deleted comments (have value 1) – fxuser Jun 4 at 22:47 @fxuser: Do you want to count comments and replies? Or show them?

– ypercube Jun 4 at 22:57 im going to leave it. I am going to show the deleted items and just show a message. – fxuser Jun 4 at 23:02 @fxuser: try the 2nd one.

– ypercube Jun 4 at 23:28.

You need to put the deleted check into the join clause. This should do it: SELECT * FROM posts p LEFT JOIN comments c ON c. Post_id = p.

Post_id AND NOT c. Deleted LEFT JOIN replies r ON r. Post_id = p.

Post_id AND NOT r. Deleted WHERE p. User_id = $user_id Note: Not sure if c.

Post_id joins to p. Id or p. Post_id - chenge the on clause as required.

Still counts the deleted comments or posts.. if I remove either one of the joins it will show correct data from comments or replies but both of them just don't work... – fxuser Jun 4 at 22:58.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.