From: livejavabean (livejavabeanyahoo.com)
Date: Fri Feb 11 2005 - 09:31:59 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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:mysql2pottcounty.com]
Sent: Thursday, February 10, 2005 6:15 PM
To: livejavabeanyahoo.com
Cc: mysqllists.mysql.com
Subject: RE: 1 to many query

> -----Original Message-----
> From: Tom Crimmins
> Sent: Thursday, February 10, 2005 17:08
> To: livejavabean
> Cc: mysqllists.mysql.com
> Subject: RE: 1 to many query
>
>
> > -----Original Message-----
> > From: livejavabean
> > Sent: Thursday, February 10, 2005 16:47
> > To: mysqllists.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/mysql?unsub=livejavabeanyahoo.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]