>I have a case file, a codes file, a client file, and a lawyer file.
>
>I want to list all my cases.
>
>If I use this:
>SELECT Clients.cLname, Lawyer.lLname, Case.caRetDate,
> Case.caTrialDat, Codes.Descr ;
> FROM Case, Clients, Lawyer, Codes ;
> WHERE Clients.CustID =Case.caCustID AND
> Lawyer.LawID =Case.caLawyerID AND ;
> Codes.Fld =[County] AND Codes.Code =Case.caTrialLocID ;
> INTO CURSOR cCase ORDER BY cLname
>
>I get all that I need ONLY IF my client has not deleted any counties from the codes file, or any lawyers from the lawyer file, or clients from the client file.
>
>I know about outer/inner joins, and can do this:
>SELECT Clients.cLname, Case.caRetDate, Case.caTrialDat ;
> FROM Case OUTER JOIN Clients ON Clients.CustID =Case.caCustID ;
> INTO CURSOR cCase ORDER BY cLname
>
>But it seems I can't work another outer join to gather the rest of the information (namely the lawyer and codes information. Barring writing routines into the deletion functions of the lawyer and codes file to prevent deletions of they're used (the client wants the luxury to delete former or deceased lawyers and inactive counties without losing integrity), does anyone know how to put more than one outer join into a SELECT - SQL command?
>
>The manual and help file, as well as the MSKB, only show how to use one join per command.
>
>Thanks in advance,
>JR
There's really no way to delete all those without losing integrity. There needs to be one main table from which all files are linked. If you delete parent records, you are going to leave orphans - that's not good. You need to write functionality into the deletion of the records to check for that case, and not allow it. IE, do not delete a lawyer if he has clients - instead, assign them a new lawyer?

Wayne