> -----Original Message----- > From: Tom Crimmins > Sent: Thursday, February 10, 2005 17:08 > To: livejavabean > Cc: mysql@lists.mysql.com > Subject: RE: 1 to many query > > > > -----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
Sorry, also forgot to add that if you want projects returned that don't have any state_flags associated with them you will need to make that first inner join a left join. > > > > > regards, > > -ljb > > --- > Tom Crimmins > Interface Specialist > Pottawattamie County, Iowa > --- 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]
