Capturing dropped records (Theta joins?)

[userjf]

I need to capture records that have dropped from joining two queries. When right-joined these records dropped and this was fine until it was decided we would like to capture the dropped records, research them, and clean them up. I found a book that refers to theta joins --- however I'm a newbie and would like to get an easier 'workable' solution. Below is my SQL:

SELECT [FP06 Athletics query#1].*
FROM [FP06 Athletics query#1] LEFT JOIN [FP06 Exclusions] ON [FP06 Athletics query#1].CONSID_TABLE = [FP06 Exclusions].EXCLUSIONS_TABLE
WHERE ((([FP06 Exclusions].EXCLUSIONS_TABLE) Is Null));

It successfully removed over 300 records. We'd like to capture the 300 records and clean them up. Thanks in advance for help in this matter.

 

[alvechurchdata]

I think you need to look at sub-queries instead. These let you select records which aren't selected in the sub-query.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Golom]

... It successfully removed over 300 records

Do you mean it didn't include 300 records? Your SQL doesn't do a delete of (i.e. remove) records. It just restricts the ones that appear. Presumably the records that you want are the ones in [FP06 Athletics query#1] that do not appear in [FP06 Exclusions] and that's what the query is showing you.

A theta join is something like

... From A JOIN B ON A.fld ?? B.fld

where the join condition ?? is an inequality such as <, >, <=, >=, etc.. Probably doesn't apply to what you are doing.

 

[userjf]

To answer both of you: yes, I would like to capture the records that dropped from FP06 Athletics query#1. Since the conditions do not meet a theta join, what is a sub query? I thought that my left join was subbing the exclusions from FP06 Ath query#1. Might it be possible for you to give me an example of one? (note: I plan on searching the archives for sub queries---but am hoping you can give me a little background on them). Thanks.

 

[userjf]

Just letting you know that we discovered how to capture the records in an easy query: 1) pulled in both queries; 2) linked them via the constituent id; 3) double-clicked the '*' in the primary query (FP06 Athletics Phonathon); 3) selected the unique id of query FP06 Exclusions checking the box to show this field AND placing in the criteria "IS NULL", then when it was run all of the dropped records were captured.