List:General Discussion« Previous MessageNext Message »
From:Udikarni Date:April 13 2004 11:36pm
Subject:Re: Multiple SELECTs in one query
View as plain text  
I am not sure about MySQL but in Oracle this will NOT work:  "SELECT "A" AS SortCode,     
* FROM Jobs"
However, this WILL:                                          "SELECT "A" AS SortCode,
Jobs.* FROM Jobs"

Try adding the table or alias in front of the "*".

In general, however, I will repeat my suggestion from before - try to do everything in one
pass - it's much more efficient.

Something like this:

"SELECT (CASE 
              WHEN Jobs.Project =  MyProject AND Jobs.JobType =  MyJobType  then "A"
              WHEN Jobs.Project => MyProject AND Jobs.JobType <> MyJobType  then
"B"
              WHEN Jobs.Project <> MyProject AND Jobs.JobType =  MyJobType  then "C"
              WHEN Jobs.Project <> MyProject AND Jobs.JobType <> MyJobType 
then "D"
         END CASE
        )  SortCode,
        Jobs.*
ORDER BY SortCode      ASC,
         Jobs.Priority ASC

Instead of reading the table 4 times, each time taking a slice and labeling it A/B/C/D you
read it once and during that one pass you attach to each row the code of A/B/C/D depending
on its content and you're done. One pass instead of 4 and no UNIONs.

You might have to tinker with the syntax if CASE is not available in MySQL to this extent
but that's the general idea.






In a message dated 4/13/2004 6:50:33 PM Eastern Daylight Time, spugh@stripped writes:

> 
> 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
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>  http://lists.mysql.com/mysql?unsub=1
> 
> 
> 
>

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