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