Many-To-Many Relation

[Don E]

Hello everyone, I am trying to grasp many-to-many relation database tables. There are 3 tables. Two of them are tables with info and the third table is the "link" or intermediate table: ***Table 1***Personid | name 1 | Frank2 | Tom3 | Roger4 | Kim ***Table 2***favcolorid | color | likes 1 | blue | 32 | red | 23 | violet | 14 | green| 0 ***Table 3***person_colorperson_id | color_id 1 | 1 1 | 2 2 | 1 2 | 2 3 | 3 4 | 1 I would like to get a single row/result from the above. For example: I would like to see ONLY Frank's favorite colors. I tried many queries myself but can't get seem to get any results. I appreciate it. Thanks.

[Ingolme]

The person_color table should have both fields as FOREIGN KEYs. Something like this should select rows from the colors table. If you want to select rows from other tables, just use the syntax: table_name.field_name AS alias

SELECT favcolor.id AS color_id, favcolor.color AS color_name, favcolor.likes AS likes FROM person,favcolor,person_color WHERE person.id = person_color.person_id AND person_color.color_id = favcolor.id

You can access them in PHP by the alias I gave with the AS keyword:

echo $row['color_id'];echo $row['color_name'];echo $row['likes'];

[Don E]

The 'person_color' table does have the id fields of the 'person' table and 'favcolor' table thus being foreign keys in the 'person_color' table. That's how I understand what foreign keys are not unless they mean something else..?Would you know how to query just for Frank's favorite colors? Frank is user1, and has two favorite colors, red(1) and blue(2).

[Ingolme]

About foreign keys, when you create the table you have to declare them as foreign keys in the CREATE TABLE statement. In the WHERE clause, putperson_color.person_id = [user id here] AND person_color.color_id = favcolor.id

[Don E]

When running this query: SELECT name, colorFROM personJOIN person_color, favcolorWHERE person.id =1AND favcolor.id =2 I get 6 rows returned of the same: Frank | red.Do I have to use DISTINCT? I tried query to get all the colors Frank has chosen but I can't seem to get it. When I run this query, trying to get all the colors Frank has chosen, I get too many results for the same person, Frank: SELECT person.name, favcolor.colorFROM person, favcolor, person_colorWHERE person.id =1AND person_color.color_idAND favcolor.id

[justsomeguy]

Try this: SELECT person.name, favcolor.colorFROM personJOIN person_color ON person_color.person_id = person.id AND person.id=1JOIN favcolor ON person_color.color_id = favcolor.id It's more efficient to have the condition (person ID 1) in the ON clause instead of the WHERE clause, but it doesn't return the expected result in all situations. If that query doesn't work, move that condition to the WHERE clause.

[Don E]

JSG, Man... what can I say... it works!

[Don E]

NM