Another thing to test is varied data.
* Only Type B
INSERT INTO curA (Id) Values (3)
INSERT INTO curb (Parent, Type) VALUES (3, 'b')
* No Child Records
INSERT INTO curA (Id) Values (4)
-----Original Message-----
From: Dave Crozier
Sent: Monday, February 09, 2009 10:22 AM
* 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: Joe Yoder
Sent: 2009-02-09 14:40
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'
_______________________________________________
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/000901c98aca$3efb8e30$bcf2aa...@com
** 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.
