Compare lists to find non-matching records

  • Thread starter Thread starter PatrickM
  • Start date Start date
P

PatrickM

I want to compare two lists and select the records in list 2 that don't have
a match in list 1. What is the general technique for this?

Thanks,
Patrick
 
SELECT BLDG.BLDG
FROM BLDG LEFT JOIN BLDG_GEO_x ON BLDG.[BLDG] = BLDG_GEO_x.[BLDG]
WHERE (((BLDG_GEO_x.BLDG) Is Null));
 
pLEASE ELABORATE on the .BLDG aspect. is that like use BLDG as AN OBJECT OF
BLDG?


ALSo, BLDG_GEO_x what is that?

IS the second QUERY BLDG_GEO_x ?

explain what is going on, please.



KARL DEWEY said:
SELECT BLDG.BLDG
FROM BLDG LEFT JOIN BLDG_GEO_x ON BLDG.[BLDG] = BLDG_GEO_x.[BLDG]
WHERE (((BLDG_GEO_x.BLDG) Is Null));

--
KARL DEWEY
Build a little - Test a little


PatrickM said:
I want to compare two lists and select the records in list 2 that don't have
a match in list 1. What is the general technique for this?

Thanks,
Patrick
Click to expand...
Click to expand...
 
You chose not to post any of your table or field names. Karl and I cannot see
your database. Karl made some guesses at plausible table and fieldnames. The
BLDG before the period would be a table name (whatever you happen to use as
your table name); the BLDG after it would be the fieldname within that table.

This is NOT VBA code and does not refer to objects - it's SQL, the language of
queries.
pLEASE ELABORATE on the .BLDG aspect. is that like use BLDG as AN OBJECT OF
BLDG?


ALSo, BLDG_GEO_x what is that?

IS the second QUERY BLDG_GEO_x ?

explain what is going on, please.



KARL DEWEY said:
SELECT BLDG.BLDG
FROM BLDG LEFT JOIN BLDG_GEO_x ON BLDG.[BLDG] = BLDG_GEO_x.[BLDG]
WHERE (((BLDG_GEO_x.BLDG) Is Null));

--
KARL DEWEY
Build a little - Test a little


PatrickM said:
I want to compare two lists and select the records in list 2 that don't have
a match in list 1. What is the general technique for this?

Thanks,
Patrick
Click to expand...
Click to expand...
Click to expand...
 
I would like to do something similar, but am running into problems.

I have buildings (BLDG) in tables BLDG_GEO and BLDG_BIO /*using the same
name for table and field can be confusing*/
I also have cities (CITY) in the same tables.
Occasionally there will be BLDG with the same name, but different cities.

I want just the entries in BLDG_BIO that don't already appear in BLDG_BIO

I tried

SELECT *
FROM BLDG_BIO
WHERE "BLDG_BIO.BLDG" <> "BLDG_GEO.BLDG" AND "BLDG_BIO.CITY" <>
"BLDG_GEO.CITY"

but all I get is everything from BLDG_BIO

what am I missing?
or am I off the mark completely?
 
I would like to do something similar, but am running into problems.

I have buildings (BLDG) in tables BLDG_GEO and BLDG_BIO /*using the same
name for table and field can be confusing*/
I also have cities (CITY) in the same tables.
Occasionally there will be BLDG with the same name, but different cities.

I want just the entries in BLDG_BIO that don't already appear in BLDG_BIO

I tried

SELECT *
FROM BLDG_BIO
WHERE "BLDG_BIO.BLDG" <> "BLDG_GEO.BLDG" AND "BLDG_BIO.CITY" <>
"BLDG_GEO.CITY"

but all I get is everything from BLDG_BIO

what am I missing?
or am I off the mark completely?
Click to expand...

Well, the text strings "BLDG_BIO.BLDG" and "BLDG_GEO.BLDG" are never going to
be equal - and that's what you're comparing, not the contents of those fields!

What you need is an "unmatched query" on the two tables. A "frustrated outer
join" is the correct approach here:

SELECT BLDG_BIO.*
FROM BLDG_BIO LEFT JOIN BLDG_GEO
ON BLDG_BIO.BLDG = BLDG_GEO.BLDG
AND BLDG_BIO.CITY = BLDG_GEO.CITY
WHERE BLDG_GEO.BLDG IS NULL;

This rather counterintuitive query joins all records in BLDG_BIO to the
corresponding record in BLDG_GEO, joining by BLDG and CITY; if there is no
such matching record, the LEFT JOIN gives NULL values from BLDG_GEO. The WHERE
criterion selects just those records which don't have a match.
 
Hi i want to do the same but dont quite understand the sql language. And not
following the BLG Example given by Karl.

Can someone help me i have supplied my two tables and fields i want to
compare and list ID'd that dont appear in the 2nd table

Table 1 - [Registration].[PPID]
Table 2 - [PP Atend].[Clients]

Thanks

Andrew
 
Back
Top