> -----Original Message----- > From: livejavabean > Sent: Thursday, February 10, 2005 16:47 > To: mysql@lists.mysql.com > Subject: 1 to many query > > Hi there.. > > > > Hope you can give me some thoughts on this. let say we have 3 tables > > > > table 1 (pk=project_id) > ======= > - project_id > - project_name > > table 2 (pk=project_id, project_state_flag) > ======= > - project_id > - project_state_flag (fk to state_flag) > > table 3 (pk=state_flag) > ======= > - state_flag > - state_flag_name > > > thank you.. but do u think it is possible to make the query return: > > - 1 row per project > - each project state row's state become a column > e.g. > > project 1, name, state a, state b, state c... > project 2, name, state a, state b, state c. > > thanks in advance..
This looks like a many to many relationship to me. Each project is associated with multiple state_flags, and each state_flag can be associated with multiple projects. If you have mysql 4.1 or greater, you can use try the following. It won't get you separate columns for each state_flag_name, but it will give you a list of all the state_flag_names associated with each project in a single column. SELECT t1.project_id, t1.project_name, GROUP_CONCAT(t3.state_flag_name) as state_flags FROM t1 INNER JOIN t2 ON (t1.project_id = t2.project_id) INNER JOIN t3 ON (t2.project_state_flag = t3.state_flag) GROUP BY t1.project_id > > regards, > -ljb --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
