How to make a JOIN without a directly mapped entity?

[Inocencio Oliveira]

Hi people!

If I have for example two entities, Client and Product, in product entity I have a relationship with client, so I have a @ManyToOne JPA relationship.

Client

Product
- client_id

But I wonder to make an "inverse" JOIN (left) query, because its possible under native SQL using <type> JOIN ON Table (id1 = id2), but how to figure out some situation under JPA?

If I heading doing something like that I got an error:

LEFT JOIN Product ON (cli_id = client_id)

Because 'ON' clause is not allowed up there, I know that I can use other maner like:

c FROM Product d LEFT JOIN d.client c

But sometimes I have to use like an native SQL, to JOIN a entity that don´t have directly link. Sorry If I was not clean in my ideas.

Regards!

[Rahul Babbar]

you might want to do something like

Select p.*, (Select c.<column> from Client c where c.id = p.id ) from Product p.

It will give the same result as when the join is Left Outer done from Product to Client..

[Inocencio Oliveira]

Thank you Rahul, I will try this way soon and repley back here.

[Inocencio Oliveira]

I did it, but I have some questions.

how can I test some conditions about client, like:

    
Select p.*, (Select c.<column> from Client c where c.id = p.id ) as cli from Product p where
cli.age = :age

Thanks again!

[Rahul Babbar]

Like this...

  Select p.*, (Select c.<column> from Client c where c.id = p.id and c.age = :age ) as cli from Product p