>>>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?
>
>For current cases, I see your point. However, for old cases, deceased lawyers, etc., they want the luxury of deleting stuff. I can't change that. I had this same conversation with them and that was it (sometimes you can't buck the system :) ). The outer (left) join seems to accommodate this circumstance. SQL should allow us to show orphaned records, and indeed it does, but only for one table? I doubt it. I think I'm just not seeing one aspect of this. If it comes to it, I'll do a couple of SELECT - SQL commands, the first which creates a cursor of only the matching items and the second which binds the Case file, the one with all the records, to the cursor created by the first command, and outer-joins it.
>
>Not too hard, but not too savvy, either. Additional input regarding the outer join would be appreciated, by Wayne or anyone else. Thanx!
>
>JR
A join containing more than one table is structured like this :

select .. ;
  from myTable1 ;
       inner|left|right|outer join myTable2 ;
       inner|left|right|outer join myTable3 ;
  on myTable2.id2 = myTable3.id3 ;
  on myTable1.id1 = myTable2.id2 ;
       where ...

"on" parts are written in reverse order. And if I don't remember wrong there should be one example in home()+"samples\data\testdata" joining four tables.
Cetin