outer or full join must be followed by path expression ERROR

kropeck · **Joined:** Mon Jul 19, 2004 2:23 am **Posts:** 1

Hello.
I've got a problem with outer join.
There are 2 tables:

TABLE_A
id | NAME
===========
1 | Mark
2 | Kate
3 | Adolf
4 | Benito

TABLE_B
id | SPORT
===========
1 | Football
2 | Volleyball

and there an application, where I can set what sport do they like (by checkboxes: I pick a name, and I got checkboxes with sport's names and I choose which I want to check). The results are written in the table like this

TABLE_RESULT
id | NAME_ID | SPORT_ID
======================
1 | 1 | 1
2 | 2 | 1
3 | 2 | 2
etc.

Now I do this sql question:
SELECT
b.id, b.sport, r.id
FROM
table_b b, OUTER table_result r
WHERE
r.sport_id=b.id
AND name_id=1

and I'll get [and this is exactly what I want to get]:
B.ID | B.SPORT | R.ID
====================
1 | Football | 1
2 | Volleyball | null

AND EXACTLY THE SAME I have to do in HQL, so I've done:
SELECT
b.id, b.sport, r.id
FROM
project.dataset.table_b as b
LEFT OUTER JOIN project.dataset.table_result as r
WHERE
r.sport_id=b.id AND name_id=1

but I got the error as written in topic. Where do I make mistake, or how should that HQL look like?
I'll be gratefull for help.

sdknott · **Joined:** Tue Oct 07, 2003 10:20 am **Posts:** 77

Please post your mappings, it's a bit hard to give you an HQL query without them.

If you've set up your User objects to have a collection of Sports, then this query shouldn't be necessary...