MySql query with subqueries

Question

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  | 
+-------+-------------------+------------------+

select username, receiver_id, sender_id, text from user left join messages on user.id = messages.receiver_id — morissette
– morissette, Commented Oct 27, 2014 at 19:24
select username, receiver_id, sender_id, text from user left join messages on user.id = messages.sender_id — morissette
– morissette, Commented Oct 27, 2014 at 19:24
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
– Gabriele Prestifilippo, Commented Oct 27, 2014 at 20:02

geoandri · Accepted Answer · 2014-10-27 20:30:14Z

2

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

edited Oct 27, 2014 at 20:30

answered Oct 27, 2014 at 19:39

geoandri

2,4282 gold badges18 silver badges30 bronze badges

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

1 Comment

Gabriele Prestifilippo Over a year ago

Thank you. That's actually working (instead of all other answers) just edited to add the user part, so

SELECT  messages.text,sender.username as Sender,receiver.username as Receiver  from messages   inner join user as sender on sender.id = messages.sender_id   inner join  user as receiver on receiver.id = messages.receiver_id WHERE ((sender.username or receiver.username)="Guy")

morissette · Accepted Answer · 2014-10-27 20:29:45Z

1

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'

edited Oct 27, 2014 at 20:29

answered Oct 27, 2014 at 20:05

morissette

1,1092 gold badges8 silver badges29 bronze badges

3 Comments

Gabriele Prestifilippo Over a year ago

Thanks, It's exactly as my second query, but I need both on one query, to have 1 result in the format I wrote in the answer. By the way, thanks for the reply

morissette Over a year ago

Updated, is that better?

Gabriele Prestifilippo Over a year ago

Thanks, partially is correct but since I need both username on the result I choose as correct answer the one of @geoandri

user3522371 · Accepted Answer · 2014-10-27 19:28:24Z

0

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

answered Oct 27, 2014 at 19:28

user3522371

Collectives™ on Stack Overflow

MySql query with subqueries

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related