List:General Discussion« Previous MessageNext Message »
From:Steve Pugh Date:April 12 2004 4:01pm
Subject:Multiple SELECTs in one query
View as plain text  
Hello, all!

I am porting my Visual Basic app over from MSDE to MySQL, and things so 
far are going quite well.  I've found most of the "gotcha" differences 
in how I need to structure my queries, but I am having trouble with one 
in particular.

In my original code, I could use one query to get a total count of 
records, a count of records meeing a criteria (Status = "Complete"), and 
an average on another field for the records meeting that criteria.  It 
looked like this in code:

SQLStr = "SELECT DoneCount=(SELECT Count(*) FROM " & flist & " WHERE 
Status = 'Complete'), " & _
               "TotalCount=(SELECT Count(*) FROM " & flist & "), " & _
               "AvgRenderTime=(SELECT Avg(renderminutes) FROM " & flist 
& " WHERE Status = 'Complete')"

The resulting SQL query would look something like this:

SQLStr = "SELECT DoneCount=(SELECT Count(*) FROM tableFLIST WHERE Status 
= 'Complete'),             TotalCount=(SELECT Count(*) FROM tableFLIST), 
AvgRenderTime=(SELECT Avg(renderminutes) FROM tableFLIST WHERE Status = 
'Complete')

Now, in MySQL, I get syntax errors in the query - most of them around 
"TotalCount=" in this example.  In my investigation, I found that I 
could break the one query apart and execute three calls to get the 
information I needed, like this:

            SQLStr = "SELECT count(*) as TotalCount FROM " & flist
            rs.Open SQLStr
            totalFrames = rs!totalcount
            rs.Close
           
            SQLStr = " SELECT Count(*) AS DoneCount FROM " & flist & " 
WHERE Status = 'Complete'"
            rs.Open SQLStr
            doneframes = rs!donecount
            rs.Close

            SQLStr = "SELECT Avg(renderminutes) as AvgRenderTime FROM " 
& flist & " WHERE Status = 'Complete'"
            rs.Open SQLStr
            rs.Close
           
So now that I've made a long story even longer, my question is simply 
this - is there a way to execute all three selects within the same 
query, as I was able to do when my database was MSDE?  It seems that it 
would be more efficient than making three hits on the database when one 
would suffice.

Many thanks for any help you 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