CODEWITHSUNDEEP
phpmysqlsql
Chiqui
Chiqui

Reputation: 39

SQL obtain users who have received o send a messages (UPDATE)

SELECT * 
FROM users 
WHERE NOT unique_id = {$outgoing_id} 
  and unique_id in (select outgoing_msg_id 
                    from messages 
                    WHERE incoming_msg_id={$outgoing_id})  
                    ORDER BY user_id DESC);

More info about the DB and tables in: I need to get the list of users who have previously been sent message (MySQL) (This is another question, but about the same)

Upvotes: 1

Views: 83

Answers (2)

Dorian
Dorian

Reputation: 9085

Here is how I did it with user id 1 as an example:

SELECT 
  first_name 
FROM 
  users 
  LEFT JOIN messages received_messages ON received_messages.from_id = 1 
  AND received_messages.to_id = users.id 
  LEFT JOIN messages sent_messages ON sent_messages.from_id = users.id 
  AND sent_messages.to_id = 1 
WHERE 
  received_messages.id IS NOT NULL 
  OR sent_messages.id IS NOT NULL

Upvotes: 0

Chiqui
Chiqui

Reputation: 39

SOLVED WITH:

SELECT * FROM users WHERE NOT unique_id = "337244286" and unique_id in (
SELECT outgoing_msg_id FROm messages WHERE incoming_msg_id="337244286") 
UNION 
SELECT * FROM users WHERE NOT unique_id = "337244286" and unique_id in (select incoming_msg_id from messages WHERE outgoing_msg_id="337244286")

Upvotes: 1

Related Questions