22

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.

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
user1775967
  • 631
  • 2
  • 6
  • 14

3 Answers3

33

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
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
user1775967
  • 631
  • 2
  • 6
  • 14
  • 3
    Something weird. I have 31 rows and I try to use your query. It returns to me 961 rows (31 x 31).... – Andrei Dec 02 '20 at 05:22
12

If i understand you correctly you just need UNION :D

(SELECT column1 AS name1, column2 AS name2 FROM table1 WHERE ...... )
UNION
(SELECT column3 AS name1, column4 AS name2 FROM table2 WHERE ...... )
UNION
....

As mentioned bellow in comment, columns need to have the same name (you can use aliases for it) and stay in the same order.

tkeram
  • 214
  • 1
  • 5
  • 1
    Provided that the columns are the same order and data types, otherwise it would choke. – DRapp Mar 06 '13 at 21:15
  • A union will append each query's results to the result set and with the assumption that the columns align. With the solution from @user1775967, columns can be selected from any of the queries with the assumption that the rows are aligned. – Mouscellaneous Jan 06 '17 at 14:35
2
select main.*,
(select col from tbl1 where tbl1.id=main.id) as col1,
(select col from tbl2 where tbl2.id=main.id) as col2,
(select col from tbl3 where tbl3.id=main.id) as col3
from master as main
user3600754
  • 21
  • 1
  • 2
    When answering a question, please add some explanation as to what your doing or why it solves the original question. – Nigel Ren Jul 19 '17 at 08:49
  • 1
    Except for some differently named columns, I do not see how this differs from accepted answer's solution. – HoldOffHunger Aug 23 '18 at 20:59