VFP Query Logic Question 
Author Message
 VFP Query Logic Question

Pardon the newbie question, but alas I've become mired down in
what would seem a simple query.

I've recently started developing in VFP, and find it much more
attractive than my past efforts in 2.6

Anyhow here is my problem.  ANY suggestions are most welcome!

I have two tables.  One with names of all the people that have
ever donated money to our organization indexed on their id
number, and another of current donators in the last 6 months or
so, indexed on the id number as well.

What I need is a list of all those who are in the name list that
aren't in the current donator list.  (Thus giving me a list of
all people who have NOT donated in the last six months.)

I tried what I thought logical, and did a query that in the join
statement queried fo names.id_no NOT equal to currentdon.id_no.

Well two hours and Two GIGS later the query could still not
finish when my drive ran out of room.  (These databases are no
bigger than 2MB each)

I assume this is NOT the way to do it!

Any help for a lost soul is very welcome!

Thanks,

- Mike White



Sat, 29 Aug 1998 03:00:00 GMT  
 VFP Query Logic Question

Quote:

> I have two tables.  One with names of all the people that have
> ever donated money to our organization indexed on their id
> number, and another of current donators in the last 6 months or
> so, indexed on the id number as well.
> What I need is a list of all those who are in the name list that
> aren't in the current donator list.  (Thus giving me a list of
> all people who have NOT donated in the last six months.)

Try this:

SELECT * FROM Names ;
 WHERE !( id_no IN ( SELECT id_no FROM currentdon ) )

Quote:
> I tried what I thought logical, and did a query that in the join
> statement queried fo names.id_no NOT equal to currentdon.id_no.
> Well two hours and Two GIGS later the query could still not
> finish when my drive ran out of room.  (These databases are no
> bigger than 2MB each)

Ouch! <g> What your query did was join every record to every other record
EXCEPT those with matching ID #'s.  

Hope this helps,

Sandra A. Lee



Sun, 30 Aug 1998 03:00:00 GMT  
 VFP Query Logic Question
USE EXISTS and an SQL subquery

SELECT * FROM namelist WHERE NOT EXISTS ;
 (SELECT * FROM Donators WHERE Donators.id = namelist.id)

I *think* that does it - haven't tried EXISTS myself.  Alternatively you
could SET RELATION TO id INTO Donators, then SCAN FOR EOF('Donators').

Stuart Fairful
Bytemark Ltd
Glasgow
+44 (141) 226 4890

Quote:
> Pardon the newbie question, but alas I've become mired down in
> what would seem a simple query.

> I've recently started developing in VFP, and find it much more
> attractive than my past efforts in 2.6

> Anyhow here is my problem.  ANY suggestions are most welcome!

> I have two tables.  One with names of all the people that have
> ever donated money to our organization indexed on their id
> number, and another of current donators in the last 6 months or
> so, indexed on the id number as well.

> What I need is a list of all those who are in the name list that
> aren't in the current donator list.  (Thus giving me a list of
> all people who have NOT donated in the last six months.)

> I tried what I thought logical, and did a query that in the join
> statement queried fo names.id_no NOT equal to currentdon.id_no.

> Well two hours and Two GIGS later the query could still not
> finish when my drive ran out of room.  (These databases are no
> bigger than 2MB each)

> I assume this is NOT the way to do it!

> Any help for a lost soul is very welcome!

> Thanks,

> - Mike White




Sun, 30 Aug 1998 03:00:00 GMT  
 VFP Query Logic Question

Quote:

> USE EXISTS and an SQL subquery

> SELECT * FROM namelist WHERE NOT EXISTS ;
>  (SELECT * FROM Donators WHERE Donators.id = namelist.id)

> I *think* that does it - haven't tried EXISTS myself.

Good enough.  You might, however, like to select
only a constant in the subselect rather than
everything.  This might speed up the query:

SELECT
        <whatever columns you want>
FROM
        namelist N
WHERE
        NOT EXISTS (
                SELECT
                        'X'
                FROM
                        donators D
                WHERE
                        D.id = N.id
        )

-- Tim

Quote:

> Stuart Fairful
> Bytemark Ltd
> Glasgow
> +44 (141) 226 4890

> > What I need is a list of all those who are in the name list that
> > aren't in the current donator list.  (Thus giving me a list of
> > all people who have NOT donated in the last six months.)



Mon, 31 Aug 1998 03:00:00 GMT  
 VFP Query Logic Question

You could also use SQL NOT IN clause. I've used it myself and it works
fairly well.

The syntax is the same as the NOT EXISTS function described. but the
syntax is :

SELECT Donators.Id FROM namelist WHERE Donators.Id NOT IN ;
        (SELECT NameList.Id FROM NameList)

This will return the ID codes of donators not in the Namelist table. You
can reverse this to return Namelist records not in the Donators table.

E-Mail if you have problems

Cheers

--
---------------------------------------------------------------------------

| It's supposed to be SPRITE as in Austin Healy, but someone else has it
--------------------------------------------------------------------



Sun, 13 Sep 1998 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Follow up question to previous thread on VFP 5.0a query questions

2. Follow up question to previous thread on VFP 5.0a query questions

3. Question on queries in VFP 5.0a

4. VFP alters my query! (newbie question)

5. VFP Query-by-Form question

6. Can Query Designer create and update Query VFP 6.0

7. 4-5-4 Logic Help

8. 4-5-4 Logic Help

9. Display problem with Cirrus Logic VGA

10. *** CIRRUS LOGIC 5434h.DRV ERRORS OCCURING NOW ***

11. PRG Logic Flow Documentor

12. Problem With Find Logic Changing Data

 

 
Powered by phpBB® Forum Software