List:General Discussion« Previous MessageNext Message »
From:Steve Pugh Date:April 13 2004 10:50pm
Subject:Re: Multiple SELECTs in one query
View as plain text  
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

Thread
Multiple SELECTs in one querySteve Pugh12 Apr
RE: Multiple SELECTs in one queryVictor Pendleton12 Apr
  • Re: Multiple SELECTs in one querySteve Pugh12 Apr
  • "INSERT INTO" dropping slashes from stringsSteve Pugh5 May
    • Re: "INSERT INTO" dropping slashes from stringsDaniel Clark5 May
      • Re: "INSERT INTO" dropping slashes from stringsSteve Pugh5 May
        • Re: "INSERT INTO" dropping slashes from stringsSteve Pugh5 May
          • Re: "INSERT INTO" dropping slashes from stringsDaniel Clark5 May
            • Re: "INSERT INTO" dropping slashes from stringsSteve Pugh5 May
              • Re: "INSERT INTO" dropping slashes from stringsDaniel Clark5 May
        • Re: "INSERT INTO" dropping slashes from stringsDaniel Clark5 May
    • Re: "INSERT INTO" dropping slashes from stringsDan Nelson5 May
      • Re: "INSERT INTO" dropping slashes from stringsSteve Pugh5 May
  • Re: "INSERT INTO" dropping slashes from stringsLou Olsten8 May
Re: Multiple SELECTs in one queryUdikarni12 Apr
  • Re: Multiple SELECTs in one querySteve Pugh14 Apr
  • Re: Multiple SELECTs in one queryHarald Fuchs14 Apr
Re: Multiple SELECTs in one queryUdikarni14 Apr
  • Re: Multiple SELECTs in one queryMichael Stassen14 Apr