Hibernate version: 1.2

Name and version of the database you are using: MS Access

Hi all,

I want to use HQL to join 3 Tables, A, B and C, where B and C have (n : 1) Relations to A

SQL would look something like:

SELECT * from (A LEFT JOIN B ON A.ID = B.ID) LEFT JOIN C ON A.ID = C.ID
WHERE ...

In HQL I tried the following

a)

SELECT b.A from B AS b LEFT JOIN A AS a, C AS c
WHERE b.A = c.A ...

b)

SELECT a from A AS a, B AS b, C AS c
WHERE a = b.A AND a = c.A ...

Both a) and b) queries do not work... Any ideas?

Thanks a lot,
Cheers, Arndt

when you say the queries don't work, what error message are you getting?

also post your mapping files so I can see the fields you're using to join the tables with.

The message is either: Cannot Execute SQL
or: unexpected token: B
or: missing path statement



Class A has no reference to B or C

A join either B on A or C on A works, it is just the problem when I want to join all 3 tables, so:

A: no ref to either B or C
B: <many-to-one class="A".../>
C: <many-to-one class="A".../>

There should nothing be wrong with the mapping files since they work:

in the DB it is like:

B has foreign key to A.Id
C has foreign key to A.Id
A has no ref to either

Hope that clarifies...

Actually I thing I will just use SQLQuery for it does not seem that HQL can handle this case - no such case in the docs anyway...

Cheers, Arndt

you're looking for all A's that are attached to both B's and C's correct?

unless you have links from A to B and A to C or a link between B and C you can't do the join in SQL without using something like a union, which you can't do using HQL.

I would suggest either using SQL, or adding the links in A to both B and C.
Then you could do something like:
select a from A as a left join a.B as b left join a.C as c where b.ID is not null and c.ID is not null

?

Thank you for reconfirming this...

As I said, I am already looking into the SQLQuery.

Again thanks and cheers,
Arndt

I agree with dannyhoult quote

because

Remember, HQL is not SQL and works on Entities, not tables. You must
use property names, not column names, and you must join on objects
paths, not on relations.