Get data from multiple SELECT sub-queries for reporting from MySQL database

mysql subquery
user1775967 picture user1775967 · Mar 6, 2013 · Viewed 31.9k times · Source

I'm trying to achieve is to create one complex query consisting of a few sub-queries. The idea is to give it to a business person to run on a weekly basis to pull reporting data.

The effect would be similar to the query below, where all data from many tables are displayed in one result.

select * from table1, table2, table3

So I need something like, but it's not working.

select 
    (select * from   table1 where ...... ) as table1,
    (select * from   table2 where....... ) as table2

Manually, I could run the sub-queries separately, then manually append the results into one big excel sheet. But I want to make it easier for the business person to do this, and minimize errors.

Is this possible in MySQL?

The reason for this is I'm converting a legacy Oracle PIVOT SQL statements into the MySQL equivalence, and the sub-queries are pretty complex.

I can provide the Oracle SQL if needed.

Much appreciated as always.

Answer

user1775967 picture user1775967 · Mar 6, 2013

After some fiddling around:

select * from
    (select * from   table1 where survey_user_id=4 ) as T1
    ,
    (select * from   table2 where survey_field_type_id=100 ) as T2
    ,
    (select * from table3  )  as T3

Related questions

MySQL Error 1093 - Can't specify target table for update in FROM clause

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries: SELECT * FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_…

Read More
Join vs. sub-query

I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why. I lack the theoretical knowledge to judge for myself if there is any difference. …

Read More
What is the error "Every derived table must have its own alias" in MySQL?

I am running this query on MySQL SELECT ID FROM ( SELECT ID, msisdn FROM ( SELECT * FROM TT2 ) ); and it is giving this error: Every derived table must have its own alias. What's causing this error?

Read More