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.
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.
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.
Main query and subquery can get data from different tables.
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.
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
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With