how to create jpql with left outer join

0
votes

I have for example 2 entities.
A entity (Mysql Table name="A")
B entity (Mysql Table name="B")
I want fetch data from A table where it is not in the B table.
I wrote sql and it is working.
SELECT * FROM A LEFT OUTER JOIN B ON A.id = B.a_id WHERE B.id IS null

How to realize it with JPQL?

4
javamysqljakarta-eejpajpql
Not sure what your asking here. Are you asking for the entire setup needed in Java to get and store results in objects? Or how to translate that query into something that can be run from an entity manager? If the second point then at first glance it looks fine as it is. - Tom Stone
yes,I want to translate it is to JPQL that can be run from an entity manager. - user813512
Then I don't see anything wrong with the query. Have you tried it? - Tom Stone

4 Answers

0
votes

If you have some kind of following structure:

Class EntityA
--------
long Id
Set<EntityB> Bs

Class EntityB
-------
long Id
EntityA A

I think following should work

SELECT a FROM EntityA a WHERE a.Bs IS EMPTY
0
votes

You may use this command

SELECT * FROM A WHERE A.id NOT IN (SELECT B.id FROM B)

If you wan to use only join let me know

0
votes

I believe the following should work:

SELECT A FROM A a LEFT JOIN a.B b WHERE b.id = null;

This should join A with B on id leaving null where A can not match with B. Then it selects the rows from A where b.id is null. Seems like what you're looking for. Also checkout the following SOs: How to make a JPA query with LEFT OUTER JOIN And as done in the following: How to create a JPA query with LEFT OUTER JOIN

Let me know whether it worked out.