SQL select rows based on 2 col criteria in a separate table

Question

I wish to select some rows from a table based on values from another table: Table1 (wish to select from here) Columns Date, Name, Pay

Table2 (contains a 'list' that determines what is selected from Table1) Columns Date, Name

The query I wish to write is to: Select Date,Name,Pay from Table1 where Date,Name is present in Table2

I got as far as being able to do it on one value

SELECT Date,Name,Pay FROM Table1 WHERE Table1.Name IN (Select Table2.name from Table2)

but Im stuck with how to add the date qualifier. The names in either table are not unique, what makes them unique is the date and name combination.

Answer 1

If I understood your question clearly, you want to apply join

select t1.Date,t1.Name,t1.Pay FROM Table1 t1 inner join Table2 t2 
ON t1.Name = t2.Name and t1.Date = t2.Date

Answer 2

The generic SQL solution uses exists:

Select Date, Name, Pay
from Table1 t1
where exists (select 1 from table2 t2 where t2.date = t1.date and t2.name = t1.name);

This will not match values in table 2 if they are NULL. For that, you would need a NULL-safe comparison operation. The ANSI standard is is not distinct from.

Some databases support in with tuples. In those databases, you can write:

Select Date, Name, Pay
from Table1 t1
where (t1.date, t1.name) in (select t2.date, t2.name from table2 t2);

Once again, this might have an issue with NULL values, depending on how you want to treat them.

Interestingly, you could extend your logic by using a correlated subquery:

SELECT Date, Name, Pay
FROM Table1 t1
WHERE t1.Name IN (Select t2.name from Table2 t2 where t2.date = t1.date);

Although this does what you want, I think the previous two approaches are clearer in their intent.

I should note that you could use a join for this. However, that would return duplicate values if you had duplicates in table2. For that reason, I prefer the exists or in methods, because these have no risk of duplicating values.

Answer 3

You can use alias (and instead of subquery a join ) for a more easy vision of your related table

SELECT a.Date, a.Name, a.Pay 
FROM Table1 a
inner join  Table2 b on a.name = b.name

in this case date is obtain from table1, changing the alias or addingi both column if you need more