CODEWITHSUNDEEP
mysqlsql
Muhammad Raihan Muhaimin
Muhammad Raihan Muhaimin

Reputation: 5719

Merge two column from different table row wise using mysql

I have tables Student

 --------------
| Name | Grade |
 --------------
 --------------
| John |  A+   |
 --------------
| Tina |  B    |
 --------------

And Type


 --------------
| Type | Class |
 --------------
 --------------
| Good |  12   |
 --------------
| Avg  |  11   |
 --------------

Now I want to get the result by merging them row-wise so the result of the SQL query would be, 

 -----------------------------
| Name | Grade | Type | Class |
 -----------------------------
| John |  A+   | Good |  12   |
 -----------------------------
| Tine |  B    | Avg  |  11   |
 -----------------------------

Upvotes: 0

Views: 633

Answers (1)

Nick
Nick

Reputation: 147286

Assuming there are the same number of columns in each table, you can link them by row number. Without an ORDER BY clause though the row numbering is indeterminate and you can't guarantee results.

In MySQL 5.7 and below:

SELECT Name, Grade, Type, Class
FROM (SELECT *, @rownum := @rownum + 1 AS rownum
      FROM Student
      CROSS JOIN (SELECT @rownum := 0) r
      ) s
JOIN (SELECT *, @rownum2 := @rownum2 + 1 AS rownum
      FROM Type
      CROSS JOIN (SELECT @rownum2 := 0) r
      ) t ON t.rownum = s.rownum

In MySQL 8.0 and above:

SELECT Name, Grade, Type, Class
FROM (SELECT *, ROW_NUMBER() OVER () AS rownum
      FROM Student) s
JOIN (SELECT *, ROW_NUMBER() OVER () rownum
      FROM Type) t ON t.rownum = s.rownum

Output:

Name    Grade   Type    Class
John    A+      Good    12
Tina    B       Avg     11

Demo on dbfiddle

Upvotes: 2

Related Questions