Re: [LUG] SQL advice

["Robin Cornelius" <robin.cornelius@xxxxxxxxx>]

On 5/15/07, James Fidell <james@xxxxxxxxxxxx> wrote:

>
> Add a field to the select which is the the earlier of Seminar_paperscall
> and Seminar_Date (or Seminar_Date if Seminar_paperscall is null) and
> order by that?  Should be possible to generate the description to
> associate with the date in a similar way.
>

Right thanks for that,

 my first unoptimised attempt is:-

SELECT Seminar_id, Seminar_name,Seminar_Venue,Seminar_Description,
UNIX_TIMESTAMP(IF(Seminar_paperscall IS
NULL,Seminar_Date,LEAST(Seminar_Date,Seminar_paperscall))),
UNIX_TIMESTAMP(Seminar_Date2),
Seminar_PDF,Seminar_type, Seminar_paperscall
FROM Seminar
WHERE
TO_DAYS(NOW()) <TO_DAYS(IF(Seminar_paperscall IS
NULL,Seminar_Date,LEAST(Seminar_Date,Seminar_paperscall)) )
AND ( Seminar_type=\"2\")
ORDER BY IF(Seminar_paperscall IS
NULL,Seminar_Date,LEAST(Seminar_Date,Seminar_paperscall))

Which seems to select the entries with a Seminar_paperscall entry and
order them correctly BUT i only get one row returned, the data gets
inserted where Seminar_paperscall is due.

Eg i now get

Seminar 1
Seminar 2
Seminar 4 paper due
Seminar 3

but no seminar 4

Is this possible from a single query, or do a need to generate a temp
table first with somthing like:- off the top of my head logic not
valid SQL

Query 1 :-

if(Seminar_paperscall IS NOT NULL) {
insert into temptable all_the_details nextaction=Seminar_paperscall
}

Query 2 ;-

insert into temptable all_the_details nextaction=Seminar_date

Query 3

SELECT * from temptable where TODAYS(NOW) < TO_DAYS(nextaction)


Many thanks,


-- 
Robin Cornelius
http://www.byteme.org.uk

-- 
The Mailing List for the Devon & Cornwall LUG
http://mailman.dclug.org.uk/listinfo/list
FAQ: http://www.dcglug.org.uk/linux_adm/list-faq.html