SQL Query Inner Join and OR

Question

How do i select using INNER JOIN and OR, i tried using below query but it doesn't return anything also no error

SELECT * FROM chatroom_message cm
INNER JOIN users_account ua
ON cm.userid_a = ua.reg_userid 
OR cm.userid_b = ua.reg_userid 
WHERE cm.userid_a = :chat_client OR cm.userid_b = :chat_client

Answer 1

SELECT * 
FROM chatroom_message cm INNER JOIN users_account ua ON cm.userid_a = ua.reg_userid 
INNER JOIN users_account ub ON cm.userid_b = ub.reg_userid 
WHERE cm.userid_a = :chat_client OR cm.userid_b = :chat_client

The change here is 2 joins, one join per user on the user table. This is because you have 2 user ids in the message table that are different (no one chats to themselves) so you need a join for userid_a and another join for userid_b.

The join you had was effectively returning 0 records because userid_a will never equal userid_b. Also you do not want an OR in your WHERE clause for this query.

The WHERE clause was fine.