jh_6990 jh_6990 - 5 months ago 38
SQL Question

select 2 table with same column without join

i have 2 table like this

---------tbl1----------------
id | name | lname |
1 j h
2 jj hh


and

---------tbl2----------------
_id | name | lname |
1 a b
2 aa bb
3 aaa ccc


i want to select from this 2 table like this:

---------result----------------
resultId | id | name | lname |
1 1 j h
2 2 jj hh
3 1 a b
4 2 aa bb
5 3 aaa ccc


but with select using join the result not this format! what can i do to access this select result?

Answer Source

You are basically looking for union all:

select row_number() over (order by which, id) as resultid,
       id, name, lname
from ((select id, name, lname, 1 as which from tbl1
      ) union all
      (select _id, name, lname, 2 as which from tbl2
      )
     ) n
order by which, id;

The only tricky part is the use of row_number() to assign the final resultid.