CODEWITHSUNDEEP
mysqlsubquery
Nadeem
Nadeem

Reputation: 145

Simple SQL Query to select data from two tables at once

I have two tables for my chat application in node js.

TABLE users : 

      - id (PK, AI)
      - username
      - useremail
      - userpass

TABLE messages : 

      - mess_id (PK, AI)
      - mess_to (FK - users.id)
      - mess_from (FK - users.id)
      - mess_txt 
      - timestamp

When a user get logged in, I want to retrieve the USERNAMES of those people who sent messages to the current users and to whom the current user sent messages.

The Query I have tried so far is

SELECT username FROM users JOIN messages ON users.id = messages.mess_to OR users.id = messages.mess_from WHERE users.id = 1

The above query returns the username of the current user , from each fields where he sent messages or he received the messages, and what I want is to get the names of the users who sent him messages and who got messages from this user.

Upvotes: 1

Views: 85

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31802

I would use a UNION subquery to get the IDs of users who had a conversation with the current user. Then join it with the users table.

select u.*
from (
    select mess_to as user_id from messages where mess_from = 1
    union
    select mess_from as user_id from messages where mess_to = 1
) sub
join users u on u.id = sub.user_id

Upvotes: 1

Related Questions