Thanks Dave, Yet another approach added to my VFP Q & A folder for a day when I can afford to study all options. I know about the pitfalls of single letter aliases and wondered whether I would get bitten in this code. I tried it this way to keep it as small as possible and to see if I could get away with it. VFP let me but you didn't! Seriously I appreciate all your help. Thank you! - Joe On Monday, February 09, 2009 10:21 AM, Dave Crozier wrote: > >Date: Mon, 9 Feb 2009 15:21:44 -0000 >From: Dave Crozier >To: [email protected] >cc: >Subject: RE: SQL help > >* Define and load demo cursors >CREATE CURSOR curA (Id I) >CREATE CURSOR curB (Parent I, Type C(1)) > >INSERT INTO curA (Id) VALUES (1) >INSERT INTO curA (Id) VALUES (2) > >INSERT INTO curB (Parent, Type) VALUES (1, 'a') >INSERT INTO curB (Parent, Type) VALUES (1, 'b') >INSERT INTO curB (Parent, Type) VALUES (2, 'a') > >SELECT curA.id, ; > curB.type as typ1, ; > curB2.type as typ2; >FROM curB ; >RIGHT JOIN curA ON curA.id == curB.parent AND curB.type = 'a' ; >LEFT JOIN curB as curB2 ON curA.id == curB2.parent AND curB2.type = 'b' > >Note that the logic is transferred from your Where condition into the join >condition. This is where it needs to be in order to get the correct data. > >Also, its bad form to use single letter aliases and this method or naming >can get you into all sorts of trouble, hence the slight renaming of cursors >in my example > >Dave Crozier > > > >-----Original Message----- >From: [email protected] [mailto:[email protected]] On Behalf >Of Joe Yoder >Sent: 2009-02-09 14:40 >To: [email protected] >Subject: SQL help > >How do I get a record for each Id even when there is no record in B for >Typ2? >TIA - Joe Yoder > >* Define and load demo cursors > CREATE CURSOR A (Id I) > CREATE CURSOR B (Parent I, Type C(1)) > INSERT INTO A (Id) VALUES (1) > INSERT INTO A (Id) VALUES (2) > INSERT INTO B (Parent, Type) VALUES (1, 'a') > INSERT INTO B (Parent, Type) VALUES (1, 'b') > INSERT INTO B (Parent, Type) VALUES (2, 'a') > >* Retrieve the data into a single cursor > SELECT A.Id, B1.Type as Typ1, B2.Type as Typ2; > FROM A, B as B1, B as B2; > WHERE A.Id = B1.Parent AND B1.Type = 'a'; > AND A.Id = B2.Parent AND B2.Type = 'b' > > > >--- 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.
