Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

mysql

subquery

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.

like image 651
user1775967 Avatar asked Mar 06 '13 20:03

user1775967


People also ask

Do sub queries return a tabular result?

A column subquery returns a single column of one or more values. A row subquery returns a single row of one or more values. A table subquery returns a table of one or more rows of one or more columns.

Can sub queries return data from multiple databases?

A subquery must return only one column. This means you cannot use SELECT * in a subquery unless the table you are referring has only one column. You may use a subquery that returns multiple columns, if the purpose is row comparison.

Can main query and sub query get data from different tables?

Main query and subquery can get data from different tables.

Which operator can retrieve multiple rows from a sub query?

Answer: D. Multiple-row subqueries return more than one row of results. Operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS.


3 Answers

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
like image 185
user1775967 Avatar answered Oct 25 '22 21:10

user1775967


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.

like image 44
tkeram Avatar answered Oct 25 '22 20:10

tkeram


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
like image 26
user3600754 Avatar answered Oct 25 '22 19:10

user3600754