Page 1 of 1

Relating multiple tables

Posted: Tue Feb 07, 2006 12:58 pm
by corman420
I have an issue, and I know it is easy to fix but I just can't get my mind around in.

Basiacally I have two MySQL tables: Manufacture Applications

My manufacture table has a list of manufacturers
examples: Sony, Philips, Sanyo

My applications table has a list of applications.
examples: Televisions, DVD Players, Telephones

I want to be able to choose certain application - Televisions - and then display results from the manufactures that make televisions. My problem would be simple if only 1 manufacture made televisions, but I have multiple manufactures for each application.

I thought of creating a feild in the applications table called "manufactures" and then have all the ID's of each manufacture seperated my commas. But I can't get any further.

Can anyone help? I can help back :)

Thanks in advance!

Corey

Posted: Tue Feb 07, 2006 1:17 pm
by Burrito
create a third table that would have the manufacturers and each item they produce (1 per row).

then you can join the tables based on which manufacturers have values in both fields of your third table.

Posted: Tue Feb 07, 2006 6:00 pm
by raghavan20
this is the common one - to - many relationship problem.
You have to have a combining table as burrito said.

ManufacturersApplications_tbl
ManufacturerId
ApplicationId

this would be like...
Manufacturers_tbl
Sony 10
Samsung 11

Application_tbl
TV 100
Microwave Oven 200

ManufacturersApplications_tbl
Man App
10 100
11 100
11 200
and so on...