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

Question

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

Welcome to SO! To help others help you, you should read this: Why should I provide a Minimal Reporducible Example for a very simple SQL query? and the edit your question. — robsiemb
– robsiemb, Commented Nov 28, 2019 at 15:01
If I find 'John' and ' Mary' in table1 and 'Mike' and 'Jane' in table2, which pairs shall be shown in the result rows? John-Mike? John-Jane? Mary-Mike? Mary-Jane? All these four combinations? Please tell us which rule you want to apply here. — Thorsten Kettner
– Thorsten Kettner, Commented Nov 28, 2019 at 15:19
By the way: Can you explain why the names are in different tables and why you want them shown in one result, though? — Thorsten Kettner
– Thorsten Kettner, Commented Nov 28, 2019 at 15:23

score 1 · Accepted Answer · 2019-11-28 15:08:03Z

1

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

edited Nov 28, 2019 at 15:08

answered Nov 28, 2019 at 14:54

user2399035

Sign up to request clarification or add additional context in comments.

10 Comments

Waseem Mir Over a year ago

UNION will not help me, as i want to see data in different column. UNION will give me all in one row.

user2399035 Over a year ago

with the updated query you will see the data in four different columns. UNION will give me all in one row wrong. You will get as many rows as the sum of results from each single query

Waseem Mir Over a year ago

Thanks, it worked, but i am seeing null values in 2nd column aganist the first column.is there a way we can remove those null and big data parallel to each other. it will reduce the row size. other wise this way my rows count will go to 800 instead of 80 for 10 tables

user2399035 Over a year ago

that's exactly how it works. Each time you change the table you change the column. If you want to see something instead of blank just change what is inside the '' part

user2399035 Over a year ago

is there a way we can remove those null and big data parallel to each other this must be handled on the presentation layer

|

Thorsten Kettner · Accepted Answer · 2019-11-28 21:10:45Z

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!

Collectives™ on Stack Overflow

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

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related