> -----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
>
> regards,
> -ljb
---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
