CODEWITHSUNDEEP
mysqljoin
Benvaulter
Benvaulter

Reputation: 249

SQL - Joining two Select results horizontal

I am creating the following two temporary tables t1 and t2 using two SELECT statements:

+------+------+
| Col1 | Col2 |
+------+------+
| A    |    1 |
| B    |    2 |
| C    |    3 |
+------+------+

and

+------+------+
| Col3 | Col4 |
+------+------+
| C    |    5 |
| D    |    6 |
| E    |    7 |
+------+------+

The two SELECT statements by nature are always returning the same number of rows. Now I want to join/combine these two results horizontally to get the following output table:

+------+------+------+------+
| Col1 | Col2 | Col3 | Col4 |
+------+------+------+------+
| A    |    1 | C    |    5 |
| B    |    2 | D    |    6 |
| C    |    3 | E    |    7 |
+------+------+------+------+

I tried working with multiple JOIN statement, but could figure out a smart way. I also tried the UNION statement, which delivered a vertical join, but not the required horizontal version.

Here two easy SELECT statement for better orientation in possible solutions:

SELECT * FROM `t1` WHERE date = DATE(NOW())

SELECT * FROM `t2` WHERE date = DATE(NOW())

Thanks in advance for your help.

Upvotes: 3

Views: 3826

Answers (2)

Akhil
Akhil

Reputation: 2602

Creating Virtual IDs for relation. This is not a recommended way though. 

SELECT col1, col2, col3, col4 FROM 
    (SELECT t1.*, (@t1VID := @t1VID + 1) t1VID  FROM t1 , (SELECT @t1VID := 0) d) e 
        JOIN (SELECT t2.*, (@t2VID := @t2VID + 1) t2VID FROM t2, (SELECT @t2VID := 0) a ) b ON t1VID  = t2VID
        JOIN (SELECT @t1VID := 0) c ;

Ideal solution would have been adding proper relation between the tables. If not, it is best to query it separately and do the necessary joining in application layer

Upvotes: 0

ikhtiyor
ikhtiyor

Reputation: 504

Try this

SET @row_number_t1:=0;
SET @row_number_t2:=0;

SELECT t1_modif.*, t2_modif.* FROM 
    (SELECT @row_number_t1:=@row_number_t1+1 AS row_number,
            t1.* FROM t1) 
    t1_modif
JOIN (SELECT @row_number_t2:=@row_number_t2+1 AS row_number,
            t2.* FROM t2) 
    t2_modif ON t2_modif.row_number = t1_modif.row_number

Note that order is not guaranteed, to do this add ORDER BY clause at the end of each FROM t1 and FROM t2 subqueries, basically we are joining by row_number, since MySQL doesn't have ROW_ID, ROW_NUM (similar to mssql, oracle, postgres) we have used session variables

Upvotes: 3

Related Questions