> -----Original Message-----
> From: Tom Crimmins
> Sent: Thursday, February 10, 2005 17:08
> To: livejavabean
> Cc: mysql@stripped
> Subject: RE: 1 to many query
>
>
> > -----Original Message-----
> > From: livejavabean
> > Sent: Thursday, February 10, 2005 16:47
> > To: mysql@stripped
> > 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
