run sql query from two tables with where clause from one table

Question

I want to get records from one table based on a record from another table. They both have SSN fields and I want to link them by SSN. Here is a rough example what I want to get:

SELECT SUM(Table1.Payments) 
  FROM Table1 
 WHERE Table1.SSN = Table2.SSN 
   AND Table2.City = 'New York'

I want to get the sum of the payments by a variable in this case, city.

Answer 1

SELECT SUM(t1.Payments) 
from Table1 t1 
JOIN Table2 t2 on t1.SSN = t2.SSN and t2.City = 'New York'

You use an inner join, to get only the matching records where Table1 Payments are in Table2 with the same SSN.

If there are multiple records in Table2 for the SSN, then you may want to use an EXISTS query.

Select SUM(t1.Payments)
from Table1 t1 
where EXISTS( SELECT 1 FROM Table2 where SSN = t1.SSN and City = 'New York' )