Hi, I have the following 4 quer 2

[vpekulas]

Hi,

I have the following 4 queries that I'm executing
separetly, however I'd like to connect those 4 queries into
one and retrieve it. How would I go about it ?
I've tryed UNION, INNER JOIN, etc. but can't get it to work.
The DB is MySQL 3.23.


SQL_1 = "SELECT COUNT(ID) AS TOTAL_USERS FROM TABLE_USERS"
SQL_2 = "SELECT COUNT(ID) AS TOTAL_TOPICS FROM TABLE_TOPIC"
SQL_3 = "SELECT COUNT(ID) AS TOTAL_REPLY FROM TABLE_REPLY"
SQL_4 = "SELECT COUNT(ID) AS TOTAL_FORUMS FROM TABLE_FORUM"


Thanks for your help.

http://www.Europeum.net

 

[skicamel]

Not all that familiar w/ mysql, but give this one a shot...

SELECT USERS = (SELECT COUNT(ID) AS TOTAL_USERS FROM TABLE_USERS),
TOPIC = (SELECT COUNT(ID) AS TOTAL_TOPICS FROM TABLE_TOPIC),
REPLY = (SELECT COUNT(ID) AS TOTAL_REPLY FROM TABLE_REPLY),
FORUM = (SELECT COUNT(ID) AS TOTAL_FORUMS FROM TABLE_FORUM)

 

[skicamel]

Sorry, was working on a test, and didn't change your syntax completely.

SELECT USERS = (SELECT COUNT(ID) FROM TABLE_USERS),
TOPIC = (SELECT COUNT(ID) FROM TABLE_TOPIC),
REPLY = (SELECT COUNT(ID) FROM TABLE_REPLY),
FORUM = (SELECT COUNT(ID) FROM TABLE_FORUM)

 

[r937]

use UNION ALL, but it's just one query

you also need to know which counts came from where, so use "source"

select 'users'         as source
     , count(ID)       as howmany
  from TABLE_USERS
union all
select 'topics'
     , count(ID)
  from TABLE_TOPIC
union all
select 'replies'
     , count(ID) 
  from TABLE_REPLY
union all
select 'forums'
     , count(ID) 
  from TABLE_FORUM

rudy

http://rudy.ca/

 

[vpekulas]

To Skicamel:

I did execute the query you suggested with the following ASP code, but it returned error:

[TCX][MyODBC]You have an error in your SQL syntax near 'SELECT COUNT(ID) FROM TABLE_USERS), TOPIC = (SELECT COUNT(ID) FROM TABLE_TOPIC),' at line 1

The code and query I executed:

	Call OPEN_DB(MyConn)
	SQL = "SELECT USERS = (SELECT COUNT(ID) FROM TABLE_USERS), TOPIC = (SELECT COUNT(ID) FROM TABLE_TOPIC), REPLY = (SELECT COUNT(ID) FROM TABLE_REPLY), FORUM = (SELECT COUNT(ID) FROM TABLE_FORUM)"
	Set RS = MyConn.Execute(SQL)
	IF NOT RS.EOF THEN
	 	TOTAL_USER = trim(RS("USERS"))		
		TOTAL_TOPIC = trim(RS("TOPIC"))
		TOTAL_REPLY = trim(RS("REPLY"))
		TOTAL_FORUM = trim(RS("FORUM"))
	END IF
	RS.Close 
	Set RS = Nothing	
	Call CLOSE_DB(MyConn)

http://www.Europeum.net

 

[skicamel]

Apparantly MySql doesn't support subqueries(?). Have you tried Rudy's approach w/ the unions? The syntax looks good there. He just took the vertical approach where I took the horizontal.

 

[r937]

my bad, vpekulas, i did not see your remark "The DB is MySQL 3.23 in your original post

you'll have to run four queries, because mysql doesn't support unions until version 4

 

[vpekulas]

That's what I tought, but I wasn't sure
Oh well ... thank you anyways.

http://www.Europeum.net