T-SQL: 'where' query - multiple columns to match on

Question

I am working in SQL Server 2012. I know I can use there 'where' clause in a query to do a multi-variable search on a single column, eg.:

select * from cars where manufacturer in ('Ford', 'Toyota')

would return all rows that have Fort or Toyota as the manufacturer. However, what if I needed to be more specific, and I wanted to see cars made from Ford in 1995, and cars made in Toyota in 1996? Doing a query like this:

select * 
from cars 
where manufacturer in ('Ford', 'Toyota') 
  and year in ('1995', 1996)

would bring me back the data I needed, but also cars Toyota made in 1995, and cars Ford made in 1996, which I do not want in my dataset. I've seen some suggestions to do a query like this:

select * 
from cars 
where (manufacturer, year) in (('Ford', '1995'), ('Toyota', 1996))

However, this does not compile... I'm thinking this is a MySQL query. I would also like to be able to take the parameters for the where query from other tables, and not have to type in 'Ford', 'Toyota', '1995' every time I want to search

Why use INinstead of AND, OR? The last statement isn't standard SQL — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 22, 2016 at 16:21

jpw · Accepted Answer · 2016-01-22 16:19:19Z

2

Maybe this is what you are looking for?

select * from cars 
where (manufacturer = 'Ford'   and year = '1995') 
   or (manufacturer = 'Toyota' and year = '1996')

answered Jan 22, 2016 at 16:19

jpw

45k6 gold badges71 silver badges90 bronze badges

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

5 Comments

user856358 Over a year ago

That works for single queries, thanks! What if I had 100 (manufacturer + year) year combinations in a separate temp table? I liked using 'in' because I can run queries against it, but I am not seeing how I can make a subquery that would automatically return the combinations without me keying them in by hand

jpw Over a year ago

@user856358 If that was the case you should probably consider using a join instead and specify the predicate as the join condition instead of in the where clause.

user856358 Over a year ago

I think I understand, so instead of doing a where query, do a table join on manufacturer in (a,b,c) and year in (x,y,z), and let the join do the filtering?

jpw Over a year ago

@user856358 Something like that; hard to say exactly how to do it without any sample data and desired results. Maybe you should post a new follow-up question if you want more precise suggestions.

user856358 Over a year ago

I wasn't even thinking about using joins to filter, I feel like this put me on the right track.

Panagiotis Kanavos · Accepted Answer · 2016-01-22 16:19:43Z

1

Just right the filter as you typed it in the question:

WHERE 
    (manufacturer='Ford' and year=1995) or 
    (manufacturer='Toyota' and year=1996)

There's no need to overcomplicate the WHERE statement. In fact, where (manufacturer, year) in (('Ford', '1995'), ('Toyota', 1996)) isn't standared SQL

answered Jan 22, 2016 at 16:19

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Collectives™ on Stack Overflow

T-SQL: 'where' query - multiple columns to match on

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related