I have two tables:
Campaign:
| CAMPAIGNID | NAME |
|---|---|
| 1 | A |
| 2 | B |
Players:
| PLAYERID | CAMPAIGNID | NAME |
|---|---|---|
| 1 | 1 | JOHN |
| 2 | 1 | JOHN |
| 3 | NULL | JOHN |
with Hibernate mapping OneToMany - one Campaign has many Players with cases where Player does no belong to any Campaign.
I would like to fetch list of Players for NAME=JOHN grouped by Campaign. My current working solution is that I fetch data from Players table and group them in Java code with check if Player.campaign is NULL then I create some dummy Campaign data in response.
I would prefer some other solution than going through all returned Player data and mapping to appropriate Campaign manually.
I tried with native query using RIGHT JOIN and I get all results (2 Campaigns in the list) but can not access the second one since its NULL.
Is there a way to populate second Campaign (NULL) with some dummy data so I can access list of Players inside second Campaign?