How to select values from one table with a condition based on the second table?-CodePudding

I have two tables: USERS (which has an unique_id more) and MESSAGES (incoming_user_id and outcoming_user_id more). I want to display all the users that have been sending messages with the unique_id, only those users!! I have tried this, but is still displaying all the users (even users with who our unique_id has no message sent):

$sql = "SELECT users.* 
        FROM users, messages 
        WHERE messages.outgoing_user_id = users.unique_id 
        OR messages.incoming_user_id = users.unique_id GROUP BY unique_id ";

CodePudding user response：

I would suggest joining the two tables (probably on the user id property) and then SELECT the messages WHERE your condition applies

CodePudding user response：

If you want to lookup rows in a table use EXISTS or IN. One simple query:

select * 
from users
where unique_id in 
(
  select outgoing_user_id
  from messages
  where incoming_user_id = 4
)
or unique_id in 
(
  select incoming_user_id
  from messages
  where outgoing_user_id = 4
);

The same thing slighty shorter:

select * 
from users
where unique_id in 
(
  select case when outgoing_user_id = 4 then incoming_user_id
                                        else outgoing_user_id end
  from messages
  where incoming_user_id = 4 or outgoing_user_id = 4
);

Or with EXISTS:

select * 
from users u
where unique_id <> 4
and exists
(
  select null
  from messages m
  where u.unique_id in (m.incoming_user_id, m.outgoing_user_id)
    and 4           in (m.incoming_user_id, m.outgoing_user_id)
);