SQL Server : joining a table by multiple parameters

Question

I have 2 tables that I need to join based on FName and LName.

This is what the tables currently look like

TableA

• Fname
• Lname
• City
• Email

TableB

• Fname
• Lname
• Address
• City
• State

I want to return 1 data set that shows me all the data for the user using FName, LName and City as a composite unique key.

SELECT TOP (10) 
    dbo.TableA.Fname, dbo.TableA.Lname, dbo.TableA.Email, dbo.TableA.Party
    , dbo.TableB.DONR_MAIL_ORD, dbo.TableB.DONR_CHARITABLE, 
FROM    dbo.TableA 
        INNER JOIN dbo.TableB 
        ON dbo.TableA.Fname = dbo.TableB.FN AND dbo.TableA.Lname = dbo.TableB.LN
WHERE  (dbo.TableA.Party = 'r') 
    AND (dbo.TableB.DONR_MAIL_ORD = 'y') 
    AND (dbo.TableB.DONR_CHARITABLE = 'y') 
    AND (dbo.TableB.DONR_POL = 'y') 

Answer 1

Assuming you just want a unique list of Fname, Lname and City from both tables. If you just join the tables, you will end up with only data that matches in both tables. If that's the case, use ali786's answer. If you are trying to find a unique list from both tables, use this answer:

SELECT DISTINCT Fname, Lname, City FROM TableA
UNION
SELECT DISTINCT Fname, Lname, City FROM TableB

Edit:

Based on your response I'm guessing you want something very close to al798's answer, just with a WHERE clause.

SELECT *
  FROM TableA A1
  JOIN TableB B2 
    ON A1.Fname = B2.Fname 
   AND A1.Lname = B2.Lname 
   AND A1.City = B2.City
 WHERE A1.Fname = "First name of person"
   AND A1.Lname = "Last name of person"
   AND A1.City  = "the city";

If this isn't what you are looking for please provide a sample data set and provide the results you are expecting because it isn't completely clear what you are looking for.

Answer 2

Try doing like this,

SELECT *
  FROM TableA A1 INNER JOIN TableB B2 ON 
  A1.Fname = B2.Fname AND 
  A1.Lname = B2.Lname AND 
  A1.City = B2.City;

you may refer this questions of SO Join table with composite key twice & this Join tables on columns of composite foreign / primary key in a query