CODEWITHSUNDEEP
mysql
Scarface
Scarface

Reputation: 3913

Need an alternative to two left joins

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.

Upvotes: 0

Views: 1750

Answers (4)

newtover
newtover

Reputation: 32094

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

Upvotes: 0

Shawn
Shawn

Reputation: 19803

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.

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 753930

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'

Upvotes: 2

Marcelo Cantos
Marcelo Cantos

Reputation: 185862

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'

Upvotes: 1

Related Questions