CODEWITHSUNDEEP
mysqlsqljoin
user3109628
user3109628

Reputation: 11

display two tables into one using select sql

I have Table A

============
| id | val |
=====+======
| 1  | abc |
| 1  | def |
| 2  | ghi |
| 2  | jkl |
============

I have Table B

============
| id | val2 |
=====+======
| 1  | rty |
| 1  | vbn |
| 2  | uio |
| 2  | zxc |
============

I want to display the two tables like this.. 

===================
| id | val | val2 |
=====+=====+=======
| 1  | abc | rty  |
| 1  | def | vbn  |
| 2  | ghi | uio  |
| 2  | jkl | zxc  |
===================

my problem is i am having redundancy..

Upvotes: 0

Views: 64

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Yes, you have a problem because you don't have a proper join key. You can do this by using variables to create one. Something like this will work for the data you provide:

select min(id), max(aval), max(bval)
from ((select id, val as aval, NULL as bval, @rna := @rna + 1 as seqnum
       from tablea a cross join (select @rna := 0)
      ) union all
      (select id, NULL val, @rnb := @rnb + 1 as seqnum
       from tableb b cross join (select @rnb := 0)
      )
     ) ab
group by seqnum;

Upvotes: 1

Rigel1121
Rigel1121

Reputation: 2016

You can simply do this using INNER JOIN. See my query below:

SELECT A.id,val,val2 FROM
(SELECT
     @row_number:=@row_number+1 AS RowNumber,
     id,
     val
FROM TableA, (SELECT @row_number:=0) AS t ORDER BY val) AS A
INNER JOIN
(SELECT
     @row_number:=@row_number+1 AS RowNumber,
     id,
     val2
FROM TableB, (SELECT @row_number:=0) AS t ORDER BY val2) AS B
ON A.RowNumber=B.RowNumber

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

I like Gordon's approach, since it doesn't assume the same number of rows in each table, but here's a JOIN version:

SELECT a.id,a.val,b.val2
FROM (SELECT @row_number:=@row_number+1 AS row_number
            ,id,val
      FROM Table1 a cross join (select @row_number := 0) b
      )a 
JOIN (SELECT @row_number2:=@row_number2+1 AS row_number2
            ,id,val2
      FROM Table2 a cross join (select @row_number2 := 0) b
      )b
 ON a.Row_Number = b.Row_Number2
  AND a.id = b.id

Here's a working version of his UNION version:

SELECT Row_Number,ID,MAX(Val) AS Val,MAX(Val2) AS Val2
FROM (SELECT @row_number:=@row_number+1 AS row_number
            ,id,val,NULL as Val2
      FROM Table1 a cross join (select @row_number := 0) b      
      UNION ALL
      SELECT @row_number2:=@row_number2+1 AS row_number
            ,id,NULL,val2
      FROM Table2 a cross join (select @row_number2 := 0) b
      )sub
GROUP BY Row_Number,ID

Demo of both: SQL Fiddle

Upvotes: 0

Related Questions