CODEWITHSUNDEEP
mysql
manizzi
manizzi

Reputation: 3

Build MySql Query

I have a database table called private_message. I run the following query on it to get a result of all the user id's that one user (47762) sent a private message to:

SELECT `to_id` FROM `private_message` WHERE `from_id` = 47762

I now want to use this query or some other way on another table called users to get the email address of all the users who received the email address from 47762.

I tried the following:

SELECT * FROM `users` WHERE `sid` = (SELECT `to_id` FROM `private_message` WHERE `from_id` = 47762)

In the above query, users.sid is the same as private_messages.to_id

I got the error #1242 - Subquery returns more than 1 row.

What I want is to get the email addresses of users that were sent a private message from user 47762 but the users table.

I'm a novice with MYSQL so would appreciate some help here.

Thanks.

Upvotes: 0

Views: 40

Answers (1)

Jakub Matczak
Jakub Matczak

Reputation: 15656

You should use IN instead od = since you can't compate (=) sid to multiple values.

SELECT * FROM `users` WHERE `sid` IN (SELECT `to_id` FROM `private_message` WHERE `from_id` = 47762)

Anyway you can do a JOIN here instead:

SELECT u.* FROM `users` AS u
JOIN `private_message` AS pm ON u.`sid` = pm.`to_id`
WHERE pm.`from_id` = 47762

It will be more efficient in most cases.

Upvotes: 1

Related Questions