EJB QL - Joining two entities

[Sankar Subbiramaniam]

Why is the following query wrong:
SELECT s.name FROM Student s, Program p where s.subjectid=p.subjectId
The following thread gives the details: invalid ejb-ql

However, the following thread says it is possible to join two entities by specifying them in FROM clause: joining two entities in FROM clause

The same logic is explained in J2EE tutorial: J2ee tutorial

Please clarify which one is correct.

BTW, are there EJB-QL verifiers which can be used to verify our Queries apart from writing example applications and verifying ?

[Devender Thareja]

While joining one to many or many to many CMR fields, you should use IN syntax. For joining by CMP fields you can use regular SQL like syntax.
Cheers.

[Sankar Subbiramaniam]

Thanks. Where do you find more details / explanation about this ?

[Devender Thareja]

You are welcome !!
HFEJB coverage is not bad. I have read HFEJB only so far but I have previous experience of SQL also which is very similar.

[ December 16, 2005: Message edited by: Devender Thareja ]

[Valentin Crettaz]

Originally posted by Sankar Subbiramaniam:
Thanks. Where do you find more details / explanation about this ?

On the same page, but a bit higher:
http://java.sun.com/j2ee/tutorial/1_3-fcs/doc/EJBQL4.html#73421

[Sankar Subbiramaniam]

HFEJb mock exam Question 58 (Page 676), clearly tells that you can join two related entities in the notmal way.
(i.e)
Select Distinct Object(c)
From Customer c, Order o
Where c.custnum = o.custnum

The same query can be also written as

Select Distinct Object(c)
From Customer c, IN (c.orders) o