CODEWITHSUNDEEP
mysqljoinsubquery
Gabriele Prestifilippo
Gabriele Prestifilippo

Reputation: 180

MySql query with subqueries

I'm having trouble trying to make a simple query. My tables are:

user

+-------+----------+
| id    | username |
+-------+----------+

messages

+-------+-------------+------------+--------+
| id    | receiver_id | sender_id  | text   |
+-------+-------------+------------+--------+

I need to get all messages coming from or received from a SEPECIFIC user (knowing his username). But also I need to get the receiver and sender username.

I used 

SELECT *  from user U, messages M where (M.sender_id OR M.receiver_id)=(select id from user where username = 'Guy1') group by M.id

This works but now I need their username so I thought about something like:

SELECT * from user U, messages M, (select username as Sender from user U1, messages M1 where M1.sender_id= U1.id)as Sub where (M.sender_id OR M.receiver_id)=(select id from user where username = 'Guy1') group by M.id

but it's not giving me what I need, how can I achieve what I'm trying to do?

In the result I need something like:

+-------+-------------------+------------------+
| text  | receiver_Username | sender_Username  | 
+-------+-------------------+------------------+

Upvotes: 0

Views: 56

Answers (3)

geoandri
geoandri

Reputation: 2428

I think the following query will get the result you need by joining two times messages table with user table. One for the senders and one for the receivers

  SELECT  messages.text,sender.username,receiver.username   from messages
  inner join user as sender on sender.id = messages.sender_id
  inner join  user as receiver on receiver.id = messages.receiver_id

Upvotes: 2

morissette
morissette

Reputation: 1109

Maybe I didn't express well, I need to get username of sender_id and username of receiver_id and the text. But only where user is = User1 All these queries you posted doesn't do this – Gabriele Prestifilippo 6 secs ago

Username and Text of sender_id:

SELECT u.username, m.text FROM user AS u
LEFT JOIN messages AS m ON u.id = m.sender_id
WHERE u.username = 'Some_User';

Username and Text of receiver_id:

SELECT u.username, m.text FROM user AS u
LEFT JOIN messages AS m ON u.id = m.receiver_id
WHERE u.username = 'Some_User';

Combined:

SELECT u.username, m.text, x.text FROM user AS u
LEFT JOIN messages AS m ON u.id = m.receiver_id
LEFT JOIN messages AS x ON u.id = x.sender_id
WHERE u.username = 'User1'

Upvotes: 1

user3522371
user3522371

Reputation: 

SELECT U.username, M.receiver_id, M.sender_id, M.text 
FROM user U 
LEFT JOIN messages AS M ON  U.id = M.sender_id

Upvotes: 0

Related Questions