I use MySQL and JPA 1.9.4
I need to imbricate multiple join to retrieve a list of objects A with a specific object C that I can find through an object B.
Entity A: id b_id Entity B: id c_id Entity C: id number
Here, I want to retrieve a list of A with a C.number equals to 1 for example.
I tried to chain left join and to imbricate select table.
Example of try A-B: This one works.
SELECT * FROM A a LEFT JOIN B b ON a.b_id = b.id;
Example of try B-C: This one works too.
SELECT * FROM B b LEFT JOIN C c ON b.c_id = c.id WHERE c.number = 1;
Example of try A-B-C: This one doesn't work.
I started just by trying to rename the new table from A-B and mysql says "duplicate column name 'id'" I did several other try but I obviously have syntax issues that I don't succeed to fix myself.
SELECT * FROM (SELECT * FROM A a LEFT JOIN B b ON a.b_id = b.id) AS a_inter;
To summary, I succeed to get A-B or B-C but never A-B-C table to then retrieve my list of A objects of interest.
Could you help me please?