# Bidirectional left join to main entity in HQL (projection DTO)
**URL:** https://discourse.hibernate.org/t/bidirectional-left-join-to-main-entity-in-hql-projection-dto/5281
**Category:** Hibernate ORM
**Created:** [April 21, 2021, 7:52am UTC](https://discourse.hibernate.org/t/bidirectional-left-join-to-main-entity-in-hql-projection-dto/5281 "2021-04-21T07:52:28Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@proustbataille](https://discourse.hibernate.org/u/proustbataille)
#### Post date: [April 21, 2021, 7:52am UTC](https://discourse.hibernate.org/t/bidirectional-left-join-to-main-entity-in-hql-projection-dto/5281/1 "2021-04-21T07:52:28Z")
This is my simplified entity model:
[[Album] imgur.com  ](https://imgur.com/a/wUmFjLP)
I am fetching all entities A of type I, i fetch its choosen fields into projection DTO. Now i need new field in DTO. I need to additionaly fetch entities A of type II IF THEY EXISTS AND ARE RELATED TO entity B with EXID: 1. So: i need to fetch all entities A of type I and associated types B and their EXID, this has not changed. But if there exists entity A of type II, that is related to entity B of EXID x, then i shall fetch A II number into new DTO field. If such A of type II does not exists (in image: second B is related to entity A of type I but not to entity A of type II, then value of this field should be null, but row should be fetched. In general there are much more entities A of type I, and they all should be fetched, while entity A of type II related to B with EXID: x is optional. I dont know how to write that in HQL. I know how to write that in pure SQL, but afaik i cant map pure SQL to projection DTO and i cant use setTransformer (library version is too old). Please help. Here is SQL that is doing what i want
[https://bpa.st/HKAQ](https://bpa.st/HKAQ)
---
### Author:  [@beikov](https://discourse.hibernate.org/u/beikov)
#### Post date: [April 21, 2021, 2:32pm UTC](https://discourse.hibernate.org/t/bidirectional-left-join-to-main-entity-in-hql-projection-dto/5281/2 "2021-04-21T14:32:06Z")
I’m not sure I fully understand your model, but it sounds like what you are looking for is a left join, just like in SQL. Something like this in HQL `select a1.id, a2.id from A a1 join B b on a1.exid = b.exid left join A a2 on a2.id = b.id`.
---
### Author:  [@proustbataille](https://discourse.hibernate.org/u/proustbataille)
#### Post date: [April 21, 2021, 3:05pm UTC](https://discourse.hibernate.org/t/bidirectional-left-join-to-main-entity-in-hql-projection-dto/5281/3 "2021-04-21T15:05:34Z")
:a1.exid - No. a1 has no exid, A entity only has b\_id, foreign key to B, and B entities has exid
So i am interested in entities a1 and a2 of different type (lets say type is x), that share B with same exid, but exid is not foreign key. A does not know exid, it only knows b\_id
So i if i have entity a1 of type x1 that has b\_id=1 and entity a2 of type x2, that has b\_id=2, and B both entities B with id 1 and B with id 2 has same exid than it should fetch
{
a1.id, a2.id
}
But lets say we have another entity a3 of type x1 that has b\_id =3. B with id 3 has some exid that no other a of type x2 share. Then i should return row for a3, and value of second column should be null
{
a1.id, a2.id,
a3.id, null
}
---
### Author:  [@beikov](https://discourse.hibernate.org/u/beikov)
#### Post date: [April 21, 2021, 3:52pm UTC](https://discourse.hibernate.org/t/bidirectional-left-join-to-main-entity-in-hql-projection-dto/5281/4 "2021-04-21T15:52:38Z")
It really doesn’t matter much how your model or your data looks like. What you are looking for is what a left join is doing, so your existing SQL can be translated almost 1:1 to HQL. The query would look something like the following:
`select a1.id, a2.id from X1 a1 join B b on a1.bId = b.id left join X2 a2 on a2.id = b.exid`
---
### Author:  [@proustbataille](https://discourse.hibernate.org/u/proustbataille)
#### Post date: [April 21, 2021, 5:58pm UTC](https://discourse.hibernate.org/t/bidirectional-left-join-to-main-entity-in-hql-projection-dto/5281/5 "2021-04-21T17:58:32Z")
thank you for help.
b.exid is not foreign key, there is no exid in a. I cant do a.id=b.exid, because exid is not id of a
also small edit: query in opening post is wrong: it returns duplicates. However idea is same: return all A a1 of types x, and for them: if there exists A a2 of type y such that a1.b\_id = b1.id and a2.b\_id=b2.id and b2.exid=b1.exid then also return such a2. Also there may be many rows B with the same exid
Also A of type x is much more than A of type y. I need to select all A of type x, if for some row there is no a2 that satisfies above conditions then return null in this column
---
### Author:  [@beikov](https://discourse.hibernate.org/u/beikov)
#### Post date: [April 22, 2021, 7:01am UTC](https://discourse.hibernate.org/t/bidirectional-left-join-to-main-entity-in-hql-projection-dto/5281/6 "2021-04-22T07:01:36Z")
I think I gave you enough information so you can resolve this on your own now. If you need further help, you would have to share a proper entity model and at least the SQL that you would like to achieve.