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 :

table A:

  • A_id
  • A_data

table B:

  • B_id
  • B_data

table C:

  • A_A_id
  • B_B_id

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

where A_id matches B_id in table C. I am carrying this out through foreign key reference, with InnoDB engine of MySQL.

thanks

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 _data area.

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.