simple question needs a simple answer 
Author Message
 simple question needs a simple answer

In a one-to-many relationship, if I select a record on the one side, how can
I tell the number of records on the many side?  and what is the easiest way
to get the key value for the last record on the many side?
Thank you

--
Hutch El-Assaad



Thu, 26 Dec 2002 03:00:00 GMT  
 simple question needs a simple answer
As a relation, there is no simple way to get the count of records on the
many side.  You could do a SQL SELECT and get the many side into it's own
cursor, that would get you it's count pretty easy:

cOneKey = parenttable.KeyId
SELECT * ;
       FROM childtable ;
       WHERE childtable.ParentKeyId=cOneKey ;
       INTO CURSOR manyside
nManyRecs = _TALLY

_TALLY is the number of child records from the many side.

The key value from the last record on the manyside would than be as simple
as GO BOTTOM in the manyside cursor.

--
Fred



Quote:
> In a one-to-many relationship, if I select a record on the one side, how
can
> I tell the number of records on the many side?  and what is the easiest
way
> to get the key value for the last record on the many side?
> Thank you

> --
> Hutch El-Assaad




Thu, 26 Dec 2002 03:00:00 GMT  
 simple question needs a simple answer

Hutch,

*!* Count of child records
SELECT ChildTable
CALCULATE CNT() FOR Child.KeyField = Parent.KeyField TO nSomeVariable

*!* Key for last record
Probably the easiest way would be some sort of descending index like
STR(Child.ParentKey) + STR(Child.ChildKey) DESCENDING.  Then the last child
record would be the first one you come to.

--

Cindy Winegarden
Microsoft Certified Professional, Visual FoxPro

Duke Children's Information Systems
Duke University Medical Center



| In a one-to-many relationship, if I select a record on the one side, how
can
| I tell the number of records on the many side?  and what is the easiest
way
| to get the key value for the last record on the many side?
| Thank you
|
| --
| Hutch El-Assaad

|
|



Thu, 26 Dec 2002 03:00:00 GMT  
 simple question needs a simple answer
It takes a little more programming, but you can do it by "counting" as
well using a;

SET SKIP TO childalias
childcount = 0
SCAN WHILE !EOF("<childalias>")
   childcount = m.childcount + 1
ENDSCAN

While "selects" work and take less coding, in most cases, this construct
will return the answer much faster.

 Beverly Howard



Fri, 27 Dec 2002 03:00:00 GMT  
 simple question needs a simple answer
You could also do:

SELECT childtable
COUNT FOR parentid="whatever" TO childcount

I sincerely doubt your loop would be faster than this, as long as there is
an index tag on the parentid field.

--
Fred


Quote:
> It takes a little more programming, but you can do it by "counting" as
> well using a;

> SET SKIP TO childalias
> childcount = 0
> SCAN WHILE !EOF("<childalias>")
>    childcount = m.childcount + 1
> ENDSCAN

> While "selects" work and take less coding, in most cases, this construct
> will return the answer much faster.

>  Beverly Howard



Fri, 27 Dec 2002 03:00:00 GMT  
 simple question needs a simple answer

I think using SQL is the best solution.
COUNT TO will move the record pointer.

Quote:
> SELECT childtable
> COUNT FOR parentid="whatever" TO childcount



Sat, 28 Dec 2002 03:00:00 GMT  
 simple question needs a simple answer
That's true.  If you have a buffered table that's record buffered, COUNT
will commit the record as the pointer is moved.  Now that still may be what
you want to do.  In Beverly's example, her method of scanning through the
records may be the only way to get an "accurate" count, if what you want to
do is count records that have been added, and you have table buffering.
These new, uncommitted records will not be seen by a SQL SELECT statement,
whereas the SCAN will see them just fine.

--
Fred


Quote:

> I think using SQL is the best solution.
> COUNT TO will move the record pointer.

> > SELECT childtable
> > COUNT FOR parentid="whatever" TO childcount



Sat, 28 Dec 2002 03:00:00 GMT  
 simple question needs a simple answer

Quote:
>>Beverly's example, her...<< <grin>

see http://www.BevHoward.com


Mon, 30 Dec 2002 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Simple labels problem...Simple answer??

2. Plz answer me this simple foxpro question on hide

3. VFP 3.0b Simple Question - Simple Answer (I HOPE!)

4. Simple Validation Process not so simple

5. Simplest example needs help

6. Simple Help Needed

7. simple dialog code needed

8. Needing simple basic program - Foxpro for Dos

9. Need help with simple network error trap

10. Very simple, but need help...

11. I need a SIMPLE, indexed database!

12. Steps needed for a simple password scheme.

 

 
Powered by phpBB® Forum Software