Mysql complex ordering

Ok, so it might not be complex to those in the know, but it's certainly more than what I'm used to!

Ok so table 'programme' has 4 related entries in a sub table 'followup'. Each of the followup entries has a date specified for them, 1 week, 1 month, 3 months and 1 year from the date of the initially created programme entry. The followup table also has a 'completed' tinyint(1) to show whether the relative entry has been completed by staff.

This data is to be presented in the following way (this part I'm ok with)...

Code:

---

programme name | 1 week date | 1 month date | 3 month date | 1 year date
------------------------------------------------------------------------
Woop          | 01/03/2008  | 23/03/2008  | 23/05/2008  | 23/02/2008
Blah          | 02/03/2008  | 24/03/2008  | 24/05/2008  | 24/02/2008
Poop          | 10/03/2008  | 03/04/2008  | 03/06/2008  | 03/03/2008

---

where each of the dates is retrieved from the sub table. Now the tricky bit, is I would like to order it by the lowest incomplete followup date. So assuming none of the follow ups have been completed, Woop will appear first. However, if Woop's 1 week followup is completed, Blah will be top of the list (because it has the lowest incomplete followup date). If each entry has its 1 week followup complete and Woop has its 1 month follow up complete, Blah will appear first. etc etc.

Hopefully that makes sense. Is that possible?

I was thinking of just using a single sub entry to hold all 4 followup dates, but what like to see if this way is possible first. Cheers.

I might not fully understand your data but it appears the dates should not be store in the same row (the way the data is presented to a user does not have to be reflected in the underlying tables - you just need to be able to extract it in the required format). If you add the completed field to the table designs it become more apparent these 2 items of data should be grouped also consider if more dates were required say 6 month date or 2 year date could these changes be easily accommodated?

Would this work: a table, I'll call it program_dates, with the following fields:

program name (char)
type (char)
prog_date (date)
complete (char Y or N) -- you mention tinyint(1) if this allows qualifier of TRUE or FALSE use that if not read something = 'Y or 'N' is more read able than = 0 or = 1.

so each program would take 4 entries

Code:

---

programme_name | type        | prog_date  |complete
----------------------------------------------------------------
Woop          | 1 week date  | 01/03/2008 | N
Woop          | 1 month date | 23/03/2008 | N
Woop          | 3 month date | 23/05/2008 | N
Woop          | 1 year date  | 23/02/2008 | N
Blah          | 1 week date  | 02/03/2008 | N
etc

---

so to list the un-complete ( complete ='N') order by date

Code:

---

select  programme_name,
        type ,
        prog_date 
from    program_dates
where    complete ='N'
order by prog_date

---

The last thing to do is extract the data in your required format - can mysql do a pivot?
If not you can join table programme_name to itself

Code:

---

select  d1weekdate.programme_name,
        d1weekdate.prog_date,  /* '1 week date'  */
        d1monthdate.prog_date  /* '1 month date' */
        d3monthdate.prog_date  /* '3 month date' */
        d1yeardate.prog_date  /* '1 year date' */
from    program_dates d1weekdate,
        program_dates d1monthdate,
        program_dates d3monthdate,
        program_dates d1yeardate,
where    d1weekdate.programme_name = d1monthdate.programme_name /* table join */
and      d1weekdate.programme_name = d3monthdate.programme_name /* table join */
and      d1weekdate.programme_name = d1yeardate.programme_name  /* table join */
and      d1weekdate.type = '1 week date'
and      d1monthdate.type = '1 month date'
and      d3monthdate.type = '3 month date'
and      d1yeardate.type = '1 year date'

---

nice one, thanks for responding. actual displaying of the data is fine, though the issue is being able to order them by the lowest open date from the sub program_dates table

I think it will probably be easiest if I do the ordering via a php array now. It makes a fair bit of sense seeing as I'm presenting the final table form from an array anyway.

Cheers though!

May be I shouldn't try and get you to redesign a database, was stuck in job mode when i posted above.

In general if you need to sort a multiple fields within a row of a table (or fields from multiple tables for that matter) you need to build up the list using the union statement then order the list

It's not mysql syntax, and I'm not sure how you are represented something that is complete.

Code:

---

select  programme name,
        1_week_date,
        'This is a 1 week date' date_type
from    followup
where    completed = false /* check */
union
select  programme name,
        1_month_date,
        'This is a 1 month date' date_type
from    followup
where    completed = false /* check */
union
select  programme name,
        3_month_date,
        'This is a 3 month date' date_type
from    followup
where    completed = false /* check */
union
select  programme name,
        1_year_date,
        'This is a 1 year date' date_type
from    followup
where    completed = false /* check */
order by 2 asc /* sort by the 2nd field, date in this case, in ascending order*/

---

The 3rd field, I have give the alias 'date_type', might not be needed, it indicates which of the four types of date the row refers to, the results would look like this:

Code:

---

Woop          | 01/03/2008 | This is a 1 week date
Blah          | 02/03/2008 | This is a 1 week date
Poop          | 10/03/2008 | This is a 1 week date
Woop          | 23/03/2008 | This is a 1 month date
Blah          | 24/03/2008 | This is a 1 month date
Poop          | 03/04/2008 | This is a 1 month date

---

You mention using php - go with what you know you might just be wasting your time using any part of the above.

thanks once again for taking the time to reply. I hadn't even considered union and that looks like it can do what I was originally hoping for. The concept of selecting from the same table using different alias' was something I wasn't aware of either, so thanks for that too!

Maybe I have misunderstood how your datatables are setup, or there is something I would realise If i had the same structure setup to test with, but surely all you need to do is order by your flags for each date and then the date.

So if you had a (0/1)flag associated with each date, then :

Order By 1wflag, 1mflag, 3mflag, 1yflag, 1wdate

or if your flags are combined into a single incrementing tinyint, then just :

Order By flag, 1wdate

Dont shoot me down in flames, but please correct me if I have misunderstood :D