Sounds like the right direction, Sylvain! What syntax would I use to do a LEFT OUTER JOIN to Reps.Rep_Code AND LinkManu.Lib_Code AND Linksect.Lib_code (all three in one query)???
TIA
>If you want all records from table A, even if no children exist, you have to use the LEFT OUTER JOIN clause.
>
>
SELECT Library.name;
> FROM library;
> LEFT OUTER JOIN Reps ON Library.rep_code = Reps.rep_code;
> INTO CURSOR Search_Me
HTH
>
>>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
Kogo Michael Hogan
"Pinky, are you pondering what I'm pondering?"
I think so Brain, but "Snowball for Windows"?
Ideate Web Site