jh_6990 jh_6990 - 7 months ago 44
SQL Question

select 2 table with same column without join

i have 2 table like this

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


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

i want to select from this 2 table like this:

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download