SQL where conditions in-list with multiple item?

Question

I would like to query a table, where i need 2 columns meet certain criteria, as an example, a table like below:

LastName FirstName Age address
LN1      FN1        18  ADD1
LN2      FN1        20  ADD2

Now I want to extract 1st address like below:

select * from mytable
where LastName in 'LN1' and FirstName in 'FN1'

Can I use such a condition:

where (LastName, FirstName) in (LN1,FN1)

please kindly help. thanks.

yes you can use where (LastName, FirstName) in ('LN1','FN1') — Vamsi Prabhala
– Vamsi Prabhala, Commented Aug 7, 2015 at 2:07

Gordon Linoff · Accepted Answer · 2015-08-07 02:10:35Z

3

Some databases support the syntax you want:

where (LastName, FirstName) in ('LN1', 'FN1')

Note that the single quotes are important, because they are string constants.

In other databases, you need to do:

where LastName = 'LN1' or FirstName = 'FN1'

or perhaps put the constants into a derived table, and use a join for the matching.

answered Aug 7, 2015 at 2:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Coeus Wang Over a year ago

To add Signle Quote helps! I am using DB2, and that two columns i use are 'varchar", so single quote is must. Previously I am using double quote ", which not help. thanks!

B5A7 Over a year ago

Available in Oracle but not SQL Server - see stackoverflow.com/questions/35194028/…

James Z · Accepted Answer · 2015-08-07 04:52:20Z

0

You can either use or or and

select * from mytable where LastName = 'LN1' or FirstName = 'FN1';
select * from mytable where LastName = 'LN1' and FirstName = 'FN1';

edited Aug 7, 2015 at 4:52

James Z

12.3k10 gold badges27 silver badges48 bronze badges

answered Aug 7, 2015 at 2:41

Rakesh_SQL_Specialist

411 bronze badge

Collectives™ on Stack Overflow

SQL where conditions in-list with multiple item?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related