>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?
>
Yep - look at the OUTER JOIN clause for use in specifying the join criteria; what you want is a LEFT OUTER JOIN, where parent records without children are included in the result set with NULLs for the values coming from the non-existnat child records.
>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
>
>TIA