Hi everyone,
I got a little problem with some joins I�m trying to do in MySQL 5.0.
The setup is as follows:
3 tables - parent_table(pk, data), child_table1(fk_parent, pk, data), child_table2(fk_parent, pk, data).
What do I want to accomplish? Well in my java-code I have a TransferObject
that reflects this structure - Parent, Child1, Child2. Parent has besides it data variable the two child objects.
The easy way is to do a LEFT JOIN i.e:
SELECT p.*, c1.pk, c1.data, c2.pk, c2.data
FROM parent_table p
LEFT JOIN child_table1 c1
ON p.pk = c1.fk_parent
LEFT JOIN child_table2 c2
ON p.pk = c2.fk_parent
In a sense this works, but the resultset overhead is huge because of the duplicate rows that is generated when there are rows in the child tables pointing to a single pk in the parent_table.
Is there any way around it that I�m missing? My options for the moment is to do multiple queries in the java-code and write the joinlogic in
java instead.
Any sugestions are welcome - (except 'switch to Hibernate!'

)
Cheers!
/Jonas