Inherit INNER JOIN fields in MySql SELECT QUERY

Question

I want to know how I can join one field of an INNER JOIN table in a SELECT QUERY.

Here's what i got so far: In this query my result would be NameA, NameB and idC, but i want to join tableA and tableC reference as shown in the image schema.

SELECT table_A.idC, table_A.nameA, table_B.nameB
FROM table_Ref
INNER JOIN table_A ON table_Ref.idA=tableA.idA
INNER JOIN table_B ON table_Ref.idB=tableB.idB;

My wanted result would be nameA,NameB, and NameC using the idC field in table_A to know which nameC belongs to that idC.

I hope you can help me or give me a reference of this topic :)!

Schema

Answer 1

you can join on table_C

SELECT nameC, nameA, nameB
FROM table_Ref r
INNER JOIN table_A a ON r.idA=a.idA
INNER JOIN table_B b ON r.idB=b.idB
INNER JOIN table_C c ON a.idC=c.idC;

• Rick James: (I suggest that "inherit" is not the proper term for database-speak.)
• Bob Klimes: or if there isnt always a rows to join to in table_C, you can do outer join

Answer 2

In this case you can use FULL OUTER JOIN in INNER JOIN what can happen is to have blanck fields in the final table due to data that are not in all tables