Hey gang, many thanks to all for pointing me in the right direction for
my previous "multiple selects" question. I moved to 4.1.1 and
implemented Udikarni's use of multiple sum()s instead of multiple
selects() and that stuff is all groovy now!
Of course, I'm beating my head on *another* wall now...wouldn't ya just
know it?
My client code checks the main table for a few different criteria, and I
used an additional "hard" select for a sorting method. Basically, each
client looks for jobs to process, starting with jobs under its "default
project" and "default jobtype", and then by its "default project" and
all other jobtypes, and finally everything else. Within each of these
sets, jobs are sorted by a "Priority" field.
My previous query looked like this (butchered pseudocode follows):
"SELECT "A" AS SortCode, * FROM Jobs WHERE Jobs.Project = MyProject AND
Jobs.JobType = MyJobType
UNION ALL SELECT "B" AS SortCode, * FROM Jobs WHERE Jobs.Project =
MyProject AND Jobs.JobType <> MyJobType
UNION ALL SELECT "C" AS SortCode, * FROM Jobs WHERE Jobs.Project <>
MyProject AND Jobs.JobType = MyJobType
UNION ALL SELECT "D" AS SortCode, * FROM Jobs WHERE Jobs.Project <>
MyProject AND Jobs.JobType <> MyJobType
ORDER BY SortCode ASC, Jobs.Priority ASC
Now, in MySQL 4.1.1, I can't even get the first line to work - I suspect
that I'm doing something wrong with that pesky asterisk, because the
following works:
"SELECT "A" AS SortCode, JobName FROM Jobs"
But the following does not:
"SELECT "A" AS SortCode, * FROM Jobs"
From what I can see in the MySQL.org docs, this should work...any
ideas? If I can get around that, I suspect that my UNIONS will work OK
and all will be well in the world....one can hope?!?
As before, many thanks for any insight that y'all can provide!!
Steve
