relational tables

[nozom]

I need to create a realtionship in mysql between 2 tables but i haven't done this before and am stumped.

I have a table that holds information about different individuals.

one field is 'company_link' which currently only holds only ONE company name. I would like this field to hold an array of company names for each database entry. hence i need to create a new table 'the_companies' which links to this.

I know how to do a work around in php but i would like to know how to build the table and select from it properly as i have been avoiding this for quite some time ;-)

If anyone can help, or point me in the direction of a good tutorial i would appreciate it. I have lucked out with my web searches so far.

Cheers,
Hugh

[ultraslacker]

http://www.ca.postgresql.org/docs/aw_pgsql_book/node57.html

Really what you should have here is a many to many relationship between companies and individuals.

individual
individual_id
..

company
company_id
...

company_individual
company_id
individual_id
...

SELECT company_name, individual_name
FROM company c, individual i, company_individual ci WHERE i.individual_id = $individual_id AND ic.individual_id = i.individual_id AND c.company_id = ic.company_id

Dunno, this may have to be rewritten for mysql's planner.