CODEWITHSUNDEEP
mysql
Waseem Mir
Waseem Mir

Reputation: 303

How to bring output of multiple select statement in different columns with row more than 1 in MYSQL

i have below select statement which i am trying to run in MYSQL. There are many rows in the output, approx 80. what i want to achive is , that all the four result come on one screen, in single execution and form 4 columns.

select `name` newname1 from table
select `name` newname2 from table2
select `name` newname3 from table3
select `name` newname4 from table4

I tried a lot looking in stack overflow, but i dint find relevant. it work for me if i limit the sub query to 1

SELECT (select name newname1 from table1 limit 1 ) as table1 ,
(select name  from newname2 from table 2 limit 1) as table2

Data look like below

table 1   
========
NAME
========
text
sms
mobile
tv
phone

table 2
========
NAME
========
something
test
road
board

table 3
========
NAME
========
landline
fixedline
elife

should be like

NAME      NAME      NAME
======   =======    ======
Text     something   landine
sms      test        fixedline
mobile   road        elife
tv       board     
phone

Upvotes: 0

Views: 722

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

It looks like you are randomly building pairs. Why is it Text-something-landine in one row and not, say, Text-test-elife? It seems you don't care at all. You just want to show three tables with no order at all side by side (in three columns).

The best way to do this is to run three separate queries and have your app or Website put the results in the three columns.

If you want to do this in SQL, you'll have to join the rows on some generated row number (usually done with ROW_NUMBER and FULL OUTER JOIN). MySQL features ROW_NUMBER as of version 8 and no FULL OUTER JOIN so far. This means you would even have to mimic the full outer join. Numbering all rows requires sorting the three datasets first. Then the same numbers must be found in the three data sets for the joins. All in all a lot of unnecessary work for the DBMS.

My recommendation: Don't do this in SQL!

Upvotes: 0

Lelio Faieta
Lelio Faieta

Reputation: 6663

Just use UNION

select `name` newname1 from table
UNION
select `name` newname2 from table2
UNION
select `name` newname3 from table3
UNION
select `name` newname4 from table4

The only point is that it will trow an error since you are using 4 different alias for the name field. You should remove the alias or assign to each the same alias

UPDATE: since OP wants to see the data in different columns.

select `name` newname1, '' newname2, '' newname3, '' newname4 from table
UNION
select '' newname1, `name` newname2,'' newname3, '' newname4 from table2
UNION
select '' newname1,'' newname2,`name` newname3, '' newname4 from table3
UNION
select '' newname1,'' newname2, '' newname3,`name` newname4 from table4

Will do the job

Upvotes: 1

Related Questions