Hey Tom,
Thanks for giving me a hand.
I think I solved the problem, a bit painful:
ok, i think i solved the problem...
if i want the chart layout to be like this on a page:
project | state_a | state_c | state_d | state_e
1 - - Y -
2 - Y - -
3 Y - - -
i should first:
1) run a select distinct state flag from table2 used by project to print out
the header
2) then select project_id, state from table3 in the order of the state_flag
pulled from the header... and
for each row:
if the project_id is different from the previous one,
jump to the next project display row in the chart
for each state (in 1)
if the current project_states.state = state..
print "Y"
else print "-".
a little painful, but I can give room to keep track of all the project state
changes and add new states anytime without the need to add new column...
man....
thanks,
-ljb
-----Original Message-----
From: Tom Crimmins [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 10, 2005 6:15 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: RE: 1 to many query
> -----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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
