Find matching records from two tables using two fields

[morlan]

Hello,

I have two tables that I am trying to compare data with.

Both tables contain a Surname, a DateOfBirth and a SequenceNumber. I want to return the records where the Surname and DateOfBirth match.

eg. If there is a John born on 11/03/1955 in table 1 and a John born 11/03/1955 in table 2, I want to return the sequence number from table 1.

Im using this query but I know it sucks..!

SELECT ID, Surname, DateOfBirth , SequenceNumber
FROM dbo.tblTable1 M INNER JOIN
dbo.tblTable2 A
WHERE (M.Custname AND M.CustDob = A.Custname AND A.CustDOB)


Can anyone help?

 

[dcx693]

If your first table is "M" and your second table is "A", try this:
SELECT M.Surname, M.DateOfBirth, M.SequenceNumber
FROM M INNER JOIN A ON (M.DateOfBirth = A.DateOfBirth) AND (M.Surname = A.Surname);

 

[morlan]

If your first table is "M" and your second table is "A", try this:
SELECT M.Surname, M.DateOfBirth, M.SequenceNumber
FROM M INNER JOIN A ON (M.DateOfBirth = A.DateOfBirth) AND (M.Surname = A.Surname);

Of course it is! Thanks..

Im am trying to do the reverse now.. I am trying to find out which records on "M" do not match records in "A"

I've tried this query but it doesn't bring back the desired results. It seems to duplicate the results to match the number of records in "A".


SELECT M.Surname, M.CustDOB, M.AppNumber
FROM dbo.tblTemp M INNER JOIN
dbo.tblAcceptRates A ON M.CustDOB <> A.CustDOB AND M.Surname<> A.Surname


Am I on the right track? Thanks

 

[dcx693]

First, you need to change your join type from INNER to LEFT (or Right, they are basically the same thing). The inner join only takes records where the values in both tables are equal, so you'll never get something that only exists in one of the tables. The Left join takes all the values from the table on the "left" side of the join, but only those values from the "right" side where they match the values from the left.

To change your join type, double-click the join line between the DOB fields, choose one of the 3 join options (read them, they will make sense) and then do the same for the line between the Surname fields. You'll see the join line now has an arrow head on one side, pointing from the table where all the records will be taken to the table where only matching values will be taken.

 

[Brianwarnock]

I'm not sure I'm qualified to butt in on this but here goes;
as well as changing the join type doesn't Morlan need to continue to look for a match ie = not <> but add a where clause stating that the match fiels in A are null.

Brian

 

[dcx693]

Brian, you are very qualified! Notice where I wrote "First", but I never finished my thought and wrote the next step which would've begun "Next"....