1

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?

Thanks,

Manu

  • 1
    you did not put the example of try A-B-C. I guess it is like this, isn'it ? SELECT * FROM A a LEFT JOIN B b ON a.b_id = b.id LEFT JOIN C c ON b.c_id = c.id WHERE c.number = 1; – MattOverF. Apr 11 at 14:07
  • OMG! This is actually so simple! Thanks!! – Manuela CodingPadawan Apr 11 at 14:21
  • 1
    glad to hear it is solved – MattOverF. Apr 11 at 14:24
  • please mark this post as solved – MattOverF. Apr 11 at 15:24
  • I needed an answer to mark it as solved. I posted an answer but I can mark it as solved only in two days. Thanks MattOverF. – Manuela CodingPadawan Apr 11 at 16:05
0

The answer is in the comment on top.

SELECT * FROM A a LEFT JOIN B b ON a.b_id = b.id LEFT JOIN C c ON b.c_id = c.id WHERE c.number = 1;

Thanks to MattOverF.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.