I need to select a value (Id) from a table based on matching items in 2 lists.


List 1 = ('Tony','Adam','Mark')
List 2 = ('Everton', 'Arsenal', 'Villa')


Id,Number, Name, Team
1, 100, Mark, Villa
2  110, Tony, Everton
3  120, Adam, Arsenal

Let's say that a query was done to pull only the name and team. I need take the results and get the number.

But I need a query using the lists from top to bottom so first items on List A AND List B, then the 2nd, 3rd and so forth.

I need both Name and Team to match in the same row.

I hope I've explained that clearly.

Select Number, Name, Team
where Name = (list A) and Team = (list B).

Select Number from Teams
 where name = 'Tony' and team = 'Everton' 

would give 110
but I need to do it on multiple values in one go.

  • Knowing which version of Microsoft SQL Server would be helpful too.
    – J.D.
    Apr 5 at 19:20

2 Answers 2


It's not really possible to do what you want in SQL Server, both syntactically and logically (given that there's no order when one isn't specified in the context of the database engine) in a single go with multiple expressions that contain a series of values.

You'd be better off putting the values in a CTE, subquery, or single table (whether that be a temp table, table variable, or a concrete user defined table), so that they're appropriately correlated.

Here's an example showing how simplified the problem becomes with a subquery:

SELECT Teams.Number, Teams.Name, Teams.Team
FROM Teams
        ('Tony', 'Everton'), 
        ('Adam', 'Arsenal'), 
        ('Mark', 'Villa')
) AS NamesWithTeams (Name, Team)
    ON Teams.Name = NamesWithTeams.Name
    AND Teams.Team = NamesWithTeams.Team

There are no lists in SQL, but you can type them as constants, or as subqueries from some other tables.

Instead of =, use in condition.

  • This doesn't exactly work in OP's case who presumably wants the Name and Team to both match concurrently within the same row. Essentially two IN clauses would result in an OR type of condition to be applied to all combinations of values of the two value expressions.
    – J.D.
    Apr 5 at 19:26
  • That is right - I need both Name and Team to match in the same row. :)
    – Mogsy Mo
    Apr 5 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.