help needed for SQL query 3

[Nifrabar]

Hi!
Next table contents:
fld1 fld2
A1 'Xabce'
A2 .null.
A3 .null.
B1 'Yuitf'
B2 'Zqqqs'
B3 .null.
C1 .null.
C2 .null.

fld2 contains either a valid characterstring or .null.
I need an sql construct showing the record where all 'A's 'B's or C's in fld 1 are having .null. in fld2

So the result for the above should be:
fld1 fld2
C1 .null.
C2 .null.

fld1 I simplified in this example. Real Value might be: A01B1. Than there could also be A01B2 A01B3 and A01B4
These form a group called A01B

any sugestions?

-Bart

 

[bborissov]

CREATE CURSOR crsTest (Fld1 C(2), Fld2 C(10) NULL)
INSERT INTO crsTest VALUES ("A1",'Xabce')
INSERT INTO crsTest VALUES ("A2",null)
INSERT INTO crsTest VALUES ("A3",null)
INSERT INTO crsTest VALUES ("B1",'Yuitf')
INSERT INTO crsTest VALUES ("B2",'Zqqqs')
INSERT INTO crsTest VALUES ("B3",null)
INSERT INTO crsTest VALUES ("C1",null)
INSERT INTO crsTest VALUES ("C2",null)

SELECT crsTest.*;
       FROM CrsTest;
INNER JOIN(SELECT LEFT(Fld1,1) AS Grp;
                  FROM crsTest;
           GROUP BY 1;
           HAVING COUNT(Fld2) = 0) Tbl1;
     ON LEFT(crsTest.Fld1,1) == Tbl1.Grp;
INTO CURSOR crsQry
BROWSE NORMAL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Nifrabar]

Borislav,
Thanks for your efforts.
I could never find such a solution!
star for you
-Bart

 

[markros]

Take one from me too.