CODEWITHSUNDEEP
phpmysqlselectleft-join
Wellenbrecher
Wellenbrecher

Reputation: 179

LEFT JOIN selecting conditions

SELECT * FROM messages as t1
LEFT JOIN
(
    SELECT topic_id date_seen FROM seen_log_ex WHERE member_id = :uid
) as t2
ON t1.topic_id=t2.topic_id AND t2.date_seen < t1.post_date

I want to select the row from messages if the table t2 doesn't contain its topic_id.

I want to select the row from messages if the table t2 contains its topic_id and t2.date_seen < t2.post_date

I DO NOT want to select the row from messages if the table t2 contains its topic_id and t2.date_seen >= t2.post_date

My query gives wrong results. How can I fix it?

Upvotes: 1

Views: 67

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32703

It seems that you still need member_id = :uid inside the subquery, but the rest of the logic should be in WHERE, not in ON of the LEFT JOIN.

It also assumes that seen_log_ex has zero or one row for each topic_id. If it can have more than one row for topic_id results would be incorrect.

SELECT * 
FROM 
    messages as t1
    LEFT JOIN
    (
        SELECT topic_id date_seen FROM seen_log_ex WHERE member_id = :uid
    ) as t2
    ON t1.topic_id=t2.topic_id
WHERE
    t2.date_seen < t1.post_date
    OR t2.topic_id IS NULL

Upvotes: 1

Serif Emek
Serif Emek

Reputation: 674

what about;

select * from messages m left join seen_log_ex l
on m.topic_id = l.topic_id
where 
(l.topic_id is null OR l.date_seen < l.post_date)
and l.member_id = :uid

Upvotes: 1

Related Questions