I'm banging my head against the wall trying to figure this one out. I have a 2 way left join query working perfectly:
'SELECT f.user_id, u.avatar
FROM following AS f
LEFT JOIN users AS u ON u.username = f.user_id
WHERE f.follower_id = "'.$u.'"';
Which is pulling a list of members 'following' you from a twitter like table, then adding their avatar from a separate members table.
Now I'm trying to pull all the data from a third table (the actual tweets), but it doesn't work:
'SELECT f.user_id, u.avatar, p.*
FROM following AS f
LEFT JOIN users AS u ON u.username = f.user_id
WHERE f.follower_id = "'.$u.'"
LEFT JOIN posts AS p ON p.username = u.username';
I have also tried adding:
WHERE p.username = "'.$u.'"';
to the end, but it doesn't make a difference. The error just says there is a SQL syntax error on the second left join. Any suggestions on what I'm missing?
join is part of the from clause. The where clause follows the from:
SELECT f.user_id, u.avatar, p.*
FROM following AS f
LEFT JOIN users AS u ON u.username = f.user_id
LEFT JOIN posts AS p ON p.username = u.username
WHERE f.follower_id = "'.$u.'" ;
You need to use the where condition after joins like this:
'SELECT f.user_id, u.avatar, p.*
FROM following AS f
LEFT JOIN users AS u ON u.username = f.user_id
LEFT JOIN posts AS p ON p.username = u.username WHERE f.follower_id = "'.$u.'" ' ;
Move the WHERE clause to be after the last LEFT JOIN.
SELECT f.user_id, u.avatar, p.*
FROM following AS f
LEFT JOIN users AS u ON u.username = f.user_id
LEFT JOIN posts AS p ON p.username = u.username
WHERE f.follower_id = "'.$u.'";
Also, for security reasons you probably want to use a prepared statement instead of concatenating the $u directly in to the query.