Need an alternative to two left joins.

Question

Hey guys quick question, I always use left join, but when I left join twice I always get funny results, usually duplicates. I am currently working on a query that Left Joins twice to retrieve the necessary information needed but I was wondering if it were possible to build another select statement in so then I do not need two left joins or two queries or if there were a better way. For example, if I could select the topic.creator in table.topic first AS something, then I could select that variable in users and left join table.scrusersonline. Thanks in advance for any advice.

SELECT * FROM scrusersonline
  LEFT JOIN users ON users.id = scrusersonline.id
  LEFT JOIN topic ON users.username = topic.creator
 WHERE scrusersonline.topic_id = '$topic_id'

The whole point of this query is to check if the topic.creator is online by retrieving his name from table.topic and matching his id in table.users, then checking if he is in table.scrusersonline. It produces duplicate entries unfortunately and is thus inaccurate in my mind.

Answer 1

One option is to group your joins thus:

SELECT *
  FROM scrusersonline
  LEFT JOIN (users ON users.id = scrusersonline.id
             JOIN topic ON users.username = topic.creator)
 WHERE scrusersonline.topic_id = '$topic_id'

Answer 2

Try:

select * from topic t 
     left outer join (
         users u 
         inner join scrusersonline o on u.id = o.id
     ) on t.creator = u.username

If o.id is null, the user is offline.

Answer 3

You use a LEFT JOIN when you want data back regardless. In this case, if the creator is offline, getting no rows back would be a good indication - so remove the LEFT joins and just do regular joins.

SELECT *
  FROM scrusersonline AS o
  JOIN users AS u ON u.id = o.id
  JOIN topic AS t ON u.username = t.creator
 WHERE o.topic_id = '$topic_id'

Answer 4

Would not it be better to match against topic_id in the topics table by moving the condition to the join. I think it will solve your problem, since duplicates come from joining with the topics table:

SELECT * FROM scrusersonline
  JOIN users
    ON users.id = scrusersonline.id
  LEFT JOIN topic
    ON scrusersonline.topic_id = '$topic_id'
      AND users.username = topic.creator

By the way, LEFT JOIN with users is not required since you seem to search for the intersection between scrusersonline and users