>My app has a parent table (A) with several child tables (B, C, D). Some parent records do not have associated records in on or more of it's child tables.
>
>I use a SQL statement to a cursor for searching purposes. I know I can probably write a seperate SQL statement to get parents without children in table B but WITH children in C and D, and then another without children in C but with children in B and D... etc - and do a UNION between them all - but that's hard to write and hard to maintain!
>
>Has anyone come up with a better way?
>
>Here's my query:
>SELECT Library.name AS Publication, LEFT(Library.descript,25) AS Description,;
> Reps.name AS Representative, Linksect.sect_code AS Division, ;
> Sections.descript AS Section_Name, Library.barcode, ;
> Manuf.name AS Manufacturer ;
> FROM library, Reps, LinkSect, Sections, Manuf, LinkManu ;
> WHERE Library.code = Linksect.Lib_code ;
> AND Library.code = LinkManu.Lib_Code ;
> AND Library.rep_code = Reps.rep_code ;
> AND Sections.Number = LinkSect.Sect_Code ;
> AND LinkManu.Manuf_Code = Manuf.Code ;
> INTO CURSOR Search_Me

For the empty child records, you can add a subquery like

WHERE...AND A.keyfield NOT IN (SELECT B.keyfield FROM B)