I'd like to start by saying that I'd like to think I am pretty proficient sql (10 years of experience) and have ran into a head scratcher. I have the following data
Group
Id Name
1 Group 1
2 Group 2
User
Id Name
1 Jon Williams
2 Mike Williams
3 Joyce Copper
Product
Id Name
1 Cookies
2 Milk
3 Ice cream
Status
Id Name
1 Untouched
2 Consumed
3 Partly Consumed
HouseholdProduct
Id Product User Status
1 1 1 1
2 2 2 1
3 3 3 1
4 1 1 1
5 2 2 1
6 3 3 1
GroupHousehold
GroupId HouseholdProductId
1 1
1 2
1 3
2 1
2 2
2 3
In Postgres, I wrote the following
select g.id as Group Id, g.name as Group, p.name as Product,
u.name as User
from group g
left join GroupHouse gh on
g.id = gh.groupId
left join HouseHoldProduct hp on
gh.houseHoldProductId = hp.id
left join User u on
hp.userId = u.Id
left join Product p on
hp.product_id = p.id
order by g.id asc
I get the following data which is correct:
ID Group Product User
1 Group 1 Cookies Jon Williams
1 Group 1 Milk Mike Williams
1 Group 1 Ice cream Joyce Copper
2 Group 2 Cookies Jon Williams
2 Group 2 Milk Mike Williams
2 Group 2 Ice cream Joyce Copper
When I take that exact query and paste it in Hibernate as native query, I get the follow results which is wrong.
ID Group Product User
1 Group 1 Cookies Jon Williams
1 Group 1 Milk Jon Williams
1 Group 1 Ice cream Jon Williams
2 Group 2 Cookies Jon Williams
2 Group 2 Milk Jon Williams
2 Group 2 Ice cream Jon Williams
It looks like hibernate is the left joining to the User table incorrectly. Does anyone know why this is happening? Is there a way to fix this?
