Selecting records with NO match 
Author Message
 Selecting records with NO match

I have a need to get all the records from a dbf with
no match (based on two fields) in second dbf, and place them in
a third dbf.

I am trying to find a quicker way... right now I am using a loop;
going record by record.

Isn't there a way to do this with select sql w/o getting
multiple joins?

The difficulty is that most uses for dbf's are to join based
on matches... but that is not what I want.  

I would appreciate any suggestions.  :)

Lemme know if u need better expl.

cmore
AVNET Cable Technologies.



Fri, 06 Nov 1998 03:00:00 GMT  
 Selecting records with NO match

What you want is an outer join.
Unfortunately, FP does not have one YET so you have to create one.
Using SQL SELECT, I SELECT all records that match your condition.
Then do a UNION ALL for those that match on cross DBF conditions.
Then GROUP by a field.
The results are all records, those with matches have the "extra" fields
filled in, those that have only a parent record - those fields are
blank.
There are examples of "doing" an outer join in most FP books + I think
it is in the examples for SQL SELECT - a fake out outer join.

...............................

Quote:

>I have a need to get all the records from a dbf with
>no match (based on two fields) in second dbf, and place them in
>a third dbf.

>I am trying to find a quicker way... right now I am using a loop;
>going record by record.

>Isn't there a way to do this with select sql w/o getting
>multiple joins?

>The difficulty is that most uses for dbf's are to join based
>on matches... but that is not what I want.  

>I would appreciate any suggestions.  :)

>Lemme know if u need better expl.

>cmore
>AVNET Cable Technologies.



Fri, 06 Nov 1998 03:00:00 GMT  
 Selecting records with NO match

Quote:

>I have a need to get all the records from a dbf with
>no match (based on two fields) in second dbf, and place them in
>a third dbf.
>I am trying to find a quicker way... right now I am using a loop;
>going record by record.
>Isn't there a way to do this with select sql w/o getting
>multiple joins?

Try something like this...

*  table1  =  The table with extra records.
*  table2  =  The table with the two fields to match against
*  results =  The results. (Records from table1 that are NOT in table2
* key1 and key2 = the fields you are matching with

SELECT * ;
 FROM table1 ;
 WHERE key1+key2 NOT IN (SELECT key1+key2 FROM table2) ;
 INTO TABLE table3

HTH, Cory



Fri, 06 Nov 1998 03:00:00 GMT  
 Selecting records with NO match

I may not understand your problem fully but can this be a solution to your
problem.

SELECT field1;
FROM dbf1;
WHERE field;
NOT EXISTS(SELECT field2;
                    FROM dbf2);
INTO TABLE dbf3

Hope this helps. Good luck.



Sun, 08 Nov 1998 03:00:00 GMT  
 Selecting records with NO match

Thanks.. will try it.   I will post the results.

cmore



Sun, 08 Nov 1998 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. results: Filtering no-match record/field (SELECT-SQL)

2. Matching tables with no common key/finding duplicate records

3. Get First Matching Record when Grouping

4. Appending from a record and replacing any entrys that match in a certain field

5. FPW2.6 - Method for matching a million records...

6. Matching up records

7. How to Count Selected Records w/in a SELECT

8. How can I limit the number of records selected with SELECT - SQL in FPW

9. how to select only records that don't match.

10. One-to-many Join: Joint parent record with *FIRST* matching child in SQL Select?

11. Displaying selected records in a list?

12. Sending words (or codes for the words) used to locate records with SELECT to a field

 

 
Powered by phpBB® Forum Software