Hello,

Let's say we have:

- Table TableA (idA is a primary key) which will be considered main.
- Table TableB (idB, idA is a composite primary key)
- Records in table B may, or may not exist.
- A.b maps as one-to-many to Table b

We want to get access to:

Specific record set from TableA with joined specific elements from TableB. By specific i mean narrowed down by TableB.pk.idB

Example:

TableA
(idA, x) VALUES (1, 'aaa');
(idA, x) VALUES (2, 'aaa');
(idA, x) VALUES (3, 'aaa');
(idA, x) VALUES (4, 'bbb');
(idA, x) VALUES (5, 'ccc');
(idA, x) VALUES (6, 'ddd');

TableB
(idA, idB, z) VALUES (1, 1, '11');
(idA, idB, z) VALUES (1, 2, '12');
(idA, idB, z) VALUES (3, 1, '31');
(idA, idB, z) VALUES (4, 1, '41');
(idA, idB, z) VALUES (5, 1, '51');
(idA, idB, z) VALUES (5, 2, '52');

SELECT a FROM
TableA a
LEFT OUTER JOIN FETCH TableA.b b
WHERE
a.x = :x AND
b.pk.idB = :idb

x = '11'
idb = 1;

Which results in SQL:
SELECT
...
FROM
TableA,
TableB
WHERE
TableA.x = 'aaa' AND
TableA.idA = TableB.idA AND
TableB.idB = 1;

QUESTIONS:
- Why there is no left join in resulting SQL ???
- SQL uses artesian product (or inner join) which filtered according to where clause returns only two rows! Which is incorrect! Result should be three records:

TableA (1, 'aaa') with TableA.b as 1 element list of TableB (1, 1, '11')
TableA (2, 'aaa') with TableA.b as 0 element list or null
TableA (3, 'aaa') with TableA.b as 1 element list of TableB (3, 1, '31')

All would be well IF hibernate would decide to use LEFT JOIN.

Can anyone shed some light on this issue ?
How can it be solved ?
Maybe i am doing something incorrect ?