Hi,

I have a query that runs fine with a "Left Outer Join" but when I do the
exact same query with a "Right Outer Join" it tells me:

"DBISAM Engine Error # 11949 SQL error - Internal join error - table
correlation name "Booking" is target of multiple join conditions in
WHERE or JOIN clause"

I can't see what i'm doing differently.

Here is the query that works:

select Booking.Shift_Date, Booking.Shift, Booking.Hospital_No,
Booking.Employee_No,
  Booking.Cancel, Booking.Booking_No, History.Shift_Date Shift_Date_2,
  History.Shift Shift_2, History.Hospital_No Hospital_No_2,
  History.Employee_No Employee_No_2, History.Payroll_End,
  History.Booking_No Booking_No_2, Nurse.Not_Invoiced, Nurse.Not_Paid,
  Nurse_2.Not_Invoiced Not_Invoiced_2, Nurse_2.Not_Paid Not_Paid_2,
Booking.Book_Date,
  substring(Booking.Hospital_No from 1 for 2) ||
substring(History.Hospital_No from 1 for 2) HospCodeAll,
  Booking.Hospital_No || History.Hospital_No HospCodeSub
from "Booking.DAT" Booking
  left outer join "History.DAT" History on (Booking.Booking_No =
History.Booking_No)
  left outer join "Nurse.DAT" Nurse on (Booking.Employee_No =
Nurse.Employee_No)
  left outer join "Nurse.DAT" Nurse_2 on (History.Employee_No =
Nurse_2.Employee_No)
where ((Booking.Shift_Date between '2002-12-01' and '2002-12-01') or
  (History.Shift_Date between '2002-12-01' and '2002-12-01'));

And here is the one that doesn't:

select Booking.Shift_Date, Booking.Shift, Booking.Hospital_No,
Booking.Employee_No,
  Booking.Cancel, Booking.Booking_No, History.Shift_Date Shift_Date_2,
  History.Shift Shift_2, History.Hospital_No Hospital_No_2,
  History.Employee_No Employee_No_2, History.Payroll_End,
  History.Booking_No Booking_No_2, Nurse.Not_Invoiced, Nurse.Not_Paid,
  Nurse_2.Not_Invoiced Not_Invoiced_2, Nurse_2.Not_Paid Not_Paid_2,
Booking.Book_Date,
  substring(Booking.Hospital_No from 1 for 2) ||
substring(History.Hospital_No from 1 for 2) HospCodeAll,
  Booking.Hospital_No || History.Hospital_No HospCodeSub
from "Booking.DAT" Booking
  right outer join "History.DAT" History on (Booking.Booking_No =
History.Booking_No)
  right outer join "Nurse.DAT" Nurse on (Booking.Employee_No =
Nurse.Employee_No)
  right outer join "Nurse.DAT" Nurse_2 on (History.Employee_No =
Nurse_2.Employee_No)
where ((Booking.Shift_Date between '2002-12-01' and '2002-12-01') or
  (History.Shift_Date between '2002-12-01' and '2002-12-01'));

as you can see it's just a matter of "left" vs "right". I need this
urgently and would really appreciate your help. I am also using DBISAM
v3.21 and Delphi 5.

Thanks in advance.

DBISAM does not support Right Outer Join.
fyi.

Jaweed,

<< I have a query that runs fine with a "Left Outer Join" but when I do the
exact same query with a "Right Outer Join" it tells me: >>

You can't have the same table as the target of two RIGHT OUTER JOIN
operations in DBISAM.  It's the same with LEFT OUTER JOINs when you try to
join multiple tables to the same target table.   You'll need to rephrase the
query.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
> Jaweed,
>
> << I have a query that runs fine with a "Left Outer Join" but when I do the
> exact same query with a "Right Outer Join" it tells me: >>
>
> You can't have the same table as the target of two RIGHT OUTER JOIN
> operations in DBISAM.  It's the same with LEFT OUTER JOINs when you try to
> join multiple tables to the same target table.   You'll need to rephrase the
> query.
>

Hi Tim,

What's the difference between the two statements that I originally
posted? They both have the exact same joins i.e Booking --> History,
Booking --> Nurse, History --> Nurse2. I can't see how the right outer
join is wrong. It works with "left" and "inner", just not "right". In
fact this is how I would phrase the query if it was an "inner" join.

Is this some sort of limitation with version 3.21?

Jaweed,

<< What's the difference between the two statements that I originally
posted? They both have the exact same joins i.e Booking --> History,
Booking --> Nurse, History --> Nurse2. I can't see how the right outer join
is wrong. It works with "left" and "inner", just not "right". In fact this
is how I would phrase the query if it was an "inner" join. >>

A ROJ is not the same as a LOJ.  It is the opposite.  With both LOJs and
ROJs in DBISAM, you cannot have the following:

MyTable  ------>  joined to MySecondTable
MyThirdTable  ----------^ also joined to MySecondTable

Such an illegal query would look like this as a LOJ:

SELECT *
FROM MyTable LEFT OUTER JOIN MySecondTable ON .....
MyThirdTable LEFT OUTER JOIN MySecondTable ON....

You would need to use a different correlation name for the second
MySecondTable instance in order for the query to be legal:

SELECT *
FROM MyTable a LEFT OUTER JOIN MySecondTable b ON .....
MyThirdTable c LEFT OUTER JOIN MySecondTable d ON....

--
Tim Young
Elevate Software
www.elevatesoft.com