Multiple SELECTs in one query

[Steve Pugh]

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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]