You will need to be using MySQL 4.1.x in order to perform sub-selects.
-----Original Message-----
From: Steve Pugh
To: [EMAIL PROTECTED]
Sent: 4/12/04 11:01 AM
Subject: Multiple SELECTs in one query
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
