Welcome Guest, Not a member yet? Register   Sign In
mysql Merge/Join table results
#1

[eluser]adamp1[/eluser]
I want to perform multiple queries on multiple tables which have nothing to do with any other query, and have all the results returned into a single result table. Each query will only ever return a single cell, never multiple columns or rows.

Now I could specify each query and run each and get the answers back, but this doesn't expand well and is messy.

So I thought about JOINING or MERGING the results. I tried the following
Code:
SELECT * FROM
(SELECT COUNT(*) AS unactive FROM be_users WHERE active=0) AS unactive
JOIN (SELECT COUNT(*) as members FROM be_users) AS members
JOIN (SELECT value AS system_status FROM be_preferences WHERE name="maintenance_mode") AS status

Now this works but I don't like the fact that the first query is defined differently to the rest. I.E. not using a JOIN. What I want is something like this.

Code:
(SELECT COUNT(*) AS unactive FROM be_users WHERE active=0)
JOIN
(SELECT COUNT(*) as members FROM be_users)
JOIN
(SELECT value AS system_status FROM be_preferences WHERE name="maintenance_mode")

So does anyone know how I can combine these queries and return a result table which has a single row, with the correct column headers. I tried UNION but that looses the column information? Any ideas?




Theme © iAndrew 2016 - Forum software by © MyBB