Select using multiple lists from top to bottom

Question

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

Example.

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

Table

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.

Answer 1

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
INNER JOIN
(
    VALUES 
        ('Tony', 'Everton'), 
        ('Adam', 'Arsenal'), 
        ('Mark', 'Villa')
) AS NamesWithTeams (Name, Team)
    ON Teams.Name = NamesWithTeams.Name
    AND Teams.Team = NamesWithTeams.Team

Answer 2

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.