how can you construct mysql command to show data with m:n relationship?
i'm able to use join to show single:d relationship, however when creating m:n relationship, another table is produced so when i attempt to complete exactly the same it, it only show one table as well as the relationship table.
For instance, I've :
I'm able to display the table so that i've
A_id | A_data | A_A_id | B_B_id
with a few data.
however the format which i want is
A_id | A_data | B_id | B_data
B_id in table C.
I am carrying this out through foreign key reference, with InnoDB engine of MySQL.
If you would like C to become the bottom table for that query (ie essentially take table C and include the related
_data rows), you should use:
SELECT A.A_id, A.A_data, B.B_id, B.B_data FROM C LEFT JOIN A ON A.A_id = C.A_A_id LEFT JOIN B ON B.B_id = C.B_B_id
This takes the table C and
JOINs it towards the other two tables, using C because the base table (due to the
LEFT JOIN). If you will find rows in C that do not appear in A or B, you'll just obtain a corresponding NULL for that
You might like to consider the various joins (INNER, LEFT, RIGHT,...) to determine what behavior you would like.
For instance you might want all ids which exist in table A to become proven, even when they are not in C (and merely display a NULL for that corresponding B posts), etc.