SELECT statement comparing multiple fields from multiple tables

Question

I have to two seperate tables (Table A and Table B). Table A has about 15 columns, the only ones of significance are [First Name] and [Last Name]. Table B has many more columns, and again the only ones I care about are FirstName and LastName (Table B's fields do not contain spaces, where as Table A's do).

They're both in Access and I'm trying to write a query that will find people from Table B who are not in Table A. I'm quite amateur with SQL, but here's what I came up with:

SELECT ([Table A].[First Name] + [Table B].[Last Name]) AS FullName
FROM [Table A] 
LEFT JOIN [Table B] 
ON [Table A].FullName=[Table B].([First Name] + [Table B].[Last Name]) AS FullName
WHERE [Table A].FullName IS NULL;

It doesn't like me trying to make an alias for First Name + Last Name from Table B, but I have no idea how I'd do it otherwise.

by spaces do you mean they are completely blank or do you mean that they have spaces in between the characters — user559142, Dec 13 '11 at 16:49
I think that your paranthesis are in the wrong place.. in fact should shouldn't need any brackets in this query. — Rich S, Dec 13 '11 at 16:50
especially the ones on the 'ON' line just after [Table B] the [First Name] field belongs to TABLE B.. just remove all your brackets and you mind find that it works. — Rich S, Dec 13 '11 at 16:52
@user1096207 He was saying that making table and field names with spaces in them is a bad idea. It forces you to clutter up your queries with all those brackets. — JohnFx, Dec 13 '11 at 16:52
Is there a required field in Table A? Last Name, perhaps, or maybe an AutonumberID? — mwolfe02, Dec 13 '11 at 16:53

score 1 · Answer 1 · answered Dec 13 '11 at 16:52

Try this:

SELECT ([Table A].[First Name] + ' ' + [Table A].[Last Name]) AS FullName
FROM [Table B] 
LEFT OUTER JOIN [Table A] 
ON [Table A].[First Name]=[Table B].[First Name] AND [Table B].[Last Name] = [Table A].[Last Name]
WHERE [Table A].[First Name] IS NULL AND [Table A].[Last Name] IS NULL

score 0 · Answer 2 · answered Dec 13 '11 at 16:53

0

SELECT ([Table A].[First Name] + [Table B].[Last Name]) AS FullName
FROM [Table A] 
RIGHT OUTER JOIN [Table B] 
ON [Table A].FullName= ([Table B].([First Name] + [Table B].[Last Name])
WHERE [Table A].FullName IS NULL;

The key here is using an outer join, which will give you all records from Table B regardless of the join. You can then filter it to the non-matching records by using the WHERE

answered Dec 13 '11 at 16:53

Maess

4,118
20
29

This will find people in a table A who are not in table B, the reverse is desired. – jmacinnes Dec 13 '11 at 16:54

Matt Donnan · Answer 3 · 2011-12-13T18:58:17.483

0

Try this:

SELECT [B].[First Name] & " " & [B].[Last Name] AS FullName
FROM TableB AS B
WHERE [B].[First Name] & " " & [B].[Last Name] NOT IN
(SELECT [First Name] & " " & [Last Name] FROM TableA)

edited Dec 13 '11 at 18:58

answered Dec 13 '11 at 16:58

Matt Donnan

4,933
3
20
32

score 0 · Answer 4 · edited May 23 '17 at 10:24

Suggestion: use VIEWs to remove those annoying spaces from the data element names, provide a consistent naming and concatenate the full name attributes e.g.

CREATE VIEW TableA
AS
SELECT [First Name] AS first_name, 
       [Last Name] AS last_name,
       first_name + ' ' + last_name AS full_name
  FROM [Table A];


CREATE VIEW TableB
AS
SELECT FirstName AS first_name, 
       LastName AS last_name,
       first_name + ' ' + last_name AS full_name
  FROM [Table B];

SELECT *
  FROM TableB AS B
 WHERE NOT EXISTS (
                   SELECT *
                     FROM TableA AS A
                    WHERE B.full_name = A.full_name
                  );

Note CREATE VIEW requires ANSI-92 Query Mode e.g. use an ADO connection.

p.s. the operator for "people from Table B who are not in Table A" is known as semi difference a.k.a. antijoin.

This query is taking ages to process, I have the latest Phpmyadmin and Navicat and both get stuck when I execute the last one. I need something like this but perhaps faster? I have 50K+ records and I am comparing tableA/B on a column that contains a string 200Characters.. Any suggestion for this case ? Thanks — Awena, Apr 01 '15 at 10:38

SELECT statement comparing multiple fields from multiple tables

4 Answers4