List:General Discussion« Previous MessageNext Message »
From:Tom Crimmins Date:February 10 2005 11:07pm
Subject:RE: 1 to many query
View as plain text  
> -----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
Thread
1 to many querylivejavabean10 Feb
RE: 1 to many queryTom Crimmins11 Feb
RE: 1 to many queryTom Crimmins11 Feb
  • RE: 1 to many querylivejavabean11 Feb