Single sql query to fetch a field value for two input value

Question

I have a table "USERS":

name | passwd | email

Is there any way to do following in a single query...

SELECT name FROM USERS WHERE email='input_email_1';
SELECT name FROM USERS WHERE email='input_email_2';

I mean, if I have two email_addresses, how can I fetch "name" from USERS table for both email_addresses in a single query.

Thanks in advance..

As well as the current `or` and `in` answers, there's also the use of `union` but it's not usually the best way so I'll just mention it in a comment. — paxdiablo, May 05 '13 at 04:02

score 4 · Answer 1 · answered May 05 '13 at 03:59

4

you can use IN

SELECT * FROM Users WHERE email IN ('email1', 'email2', 'more list....')

answered May 05 '13 at 03:59

John Woo

258,903
69
498
492

1

+1 equivalent to OR, but +1 anyway ;) ! Not so sure I like the SELECT * though... – Mitch Wheat May 05 '13 at 04:00
Thanks for quick reply...But I think, I was not clear in my question...let me tell you exact scenario...I am doing a task in which I am getting 2 email addresses for one for "sender" and second for "receiver" ...I have to get "name" from sender and receiver separately....it would be like...SELECT senderName (for sender email), receiverName (for receiver email) FROM USERS.... – Sonu May 05 '13 at 04:12
why not do a condition in the application level? anyway, how about this one? `SELECT MAX(CASE WHEN email = 'email1' THEN name END) SenderName, MAX(CASE WHEN email = 'email2' THEN name END) ReceiverName FROM Users WHERE email IN ('email1', 'email2')` – John Woo May 05 '13 at 04:19
@MitchWheat Not quite equivalent. `IN` is much faster than `OR`: http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance – Bailey Parker May 05 '13 at 04:39
1

@PhpMyCoder: probbaly incorrect. How do you think an IN statement is executed? BTW, speed has nothing to do with semantic equivalence. – Mitch Wheat May 05 '13 at 04:41
1

@PhpMyCoder I also tested the performance between `IN` and `OR` and gave the same runtime. – John Woo May 05 '13 at 04:42
@PhpMyCoder: Don't really on performance tests that do NOT show the code that was used to produce the benchamrk, and does not explcitly describe flushing memory for cached results. – Mitch Wheat May 05 '13 at 04:44
@MitchWheat Good point. Probably best to test on your own setup to see (and that way you can guarantee its benchmarked properly). – Bailey Parker May 05 '13 at 04:46

score 2 · Answer 2 · answered May 05 '13 at 03:59

2

SELECT name FROM USERS WHERE email='input_email_1' OR email='input_email_2';

answered May 05 '13 at 03:59

Mitch Wheat

295,962
43
465
541

Thanks for quick reply...But I think, I was not clear in my question...let me tell you exact scenario...I am doing a task in which I am getting 2 email addresses for one for "sender" and second for "receiver" ...I have to get "name" from sender and receiver separately.... – Sonu May 05 '13 at 04:04

score 0 · Answer 3 · answered May 05 '13 at 04:23

I might be completely wrong but it looks like (from your comments) you're looking for something like this

SELECT u1.name sender_name, 
       u2.name receiver_name
  FROM messages m JOIN
       users u1 ON m.sender = u1.email JOIN
       users u2 ON m.receiver = u2.email

Single sql query to fetch a field value for two input value

3 Answers3