How to select sql data by using 2 join tables with OR condition without duplicate records

Question

I have a mysql select statement as below:-

select user.username, chat.from_user,chat.to_user,chat.message,chat.date_created 
from chat join user on (chat.from_user or chat.to_user) in (user.user_id)
    where(chat.from_user = 3 or chat.to_user = 3) and chat.chat_id IN
(SELECT distinct (MAX(chat.chat_id) )
    FROM chat GROUP BY chat_group_id);

and here is my result

I always get username = admin. My expectation result is username will get correct from / to user.

Please help. Thank you.

Hi AKZhang, nope, the result will return username = admin (userid =1). What i want is if the user id = 2, then username will return username from user id = 3 (according to the screenshot - record 1 as attached) — Test 87
– Test 87, Commented Feb 22, 2018 at 3:53

Kasnady · Accepted Answer · 2018-02-22 04:55:29Z

1

SELECT 
    IF(chat.from_user=3, to_user
        , IF(chat.to_user=3, form_user, 0)) AS username,
    chat.from_user,chat.to_user,chat.message,chat.date_created 
    FROM chat 
    LEFT JOIN user fr_user ON chat.from_user = user.user_id
    LEFT JOIN user to_user ON chat.to_user = user.user_id -- since you only want to show the TO_USERNAME, you can remove above line
        WHERE (chat.from_user = 3 OR chat.to_user = 3) and chat.chat_id 
    IN
    (SELECT distinct (MAX(chat.chat_id) )
        FROM chat GROUP BY chat_group_id);

edited Feb 22, 2018 at 4:55

answered Feb 22, 2018 at 4:00

Kasnady

2,2893 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Test 87 Over a year ago

Hi kasnady, thx for your help. your statement is close to what i want. for username, how can i display "opposite" username. Example: if my statement is (chat.from_user = 3 OR chat.to_user = 3), then my username will display username other than 3?

Gordon Linoff · Accepted Answer · 2018-02-22 03:25:05Z

0

I think you intend:

select u.username, c.from_user, c.to_user, chat.message, c.date_created 
from chat c join
     user u
     on u.user_id in (c.from_user, c.to_user) 
where 3 in (c.from_user, c.to_user) and 
      c.chat_id in (select max(c2.chat_id)
                    from chat c2
                    group by chat_group_id
                   );

answered Feb 22, 2018 at 3:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Test 87 Over a year ago

Hi Gordon, thx for your help. However, i get duplicate records (duplication message column) from your select statement.

Collectives™ on Stack Overflow

How to select sql data by using 2 join tables with OR condition without duplicate records

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related