On Mon, Jul 20, 2009 at 3:09 PM, KAM.covad<[email protected]> wrote: > There is only one table. The parent and child records are in the same table. > The cParent field of the child record points to the parent record.
Let me explain a little: > select * > from table parent > join table child This type of SQL is called a self-join: the table is joined -to itself-, once aliased as Parent, once aliased as Child. > join table child > on parent.cPerson = child.cParent One row is returned for each parent-child relationship with parent and child details: > where parent.cPerson != parent.cParent > and nBalance < 100 Where the parent balance is < 100, and this is not the parent <-> parent relationship (where cPerson == cParent) --stuart On Mon, Jul 20, 2009 at 3:09 PM, KAM.covad<[email protected]> wrote: > There is only one table. The parent and child records are in the same table. > The cParent field of the child record points to the parent record. > > ----- Original Message ----- > From: Stuart Dunkeld > To: ProFox Email List > Sent: Monday, July 20, 2009 6:59 AM > Subject: Re: VFP9 SP2 need SQL guru help > > > how about: > > select * > from table parent > join table child > on parent.cPerson = child.cParent > where parent.cPerson != parent.cParent > and nBalance < 100 > > On Mon, Jul 20, 2009 at 2:44 PM, > KAM.covad<[email protected]> wrote: >> I have a table with 3 fields and about 20,000 records. >> >> The fields are cPerson C(9), cParent C(9), nBalance N(10,2). The cPerson >> field has a candidate index (unique). If this is a parent record, the >> cPerson is the same as cParent, otherwise it is a child record. >> >> To get all the parents with balance less than $100, I can: >> Select * from table where cPerson = cParent and nBalance < 100 >> >> Now I need all the children of those parents in the same cursor >> >> No virus found in this outgoing message >> Checked by PC Tools AntiVirus (6.0.0.19 - 10.004.060). >> http://www.pctools.com/free-antivirus/ >> >> >> --- StripMime Report -- processed MIME parts --- >> multipart/alternative >> text/plain (text body -- kept) >> text/html >> --- >> [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
