CODEWITHSUNDEEP
sqlsql-servert-sql
Gyuzal
Gyuzal

Reputation: 1591

Select data from two tables with no join condition, t-sql

I'd appreciate if someone could help. I have two tables that have no relationship:

Table_1

ID    NAME    VALUE
1     abc     10
2     def     20
3     def     20

Table_2

   ID2    NAME2    VALUE2
    5     ghi     30
    6     gkl     40

I want to have a select statement that would show the data from both tables like this:

   ID    NAME    VALUE  ID2   NAME2   VALUE2
    1     abc     10    5     ghi     30
    2     def     20    6     gkl     40
    3     def     20

The point is to show data of each record in one row, the table can look like:

 ID    NAME    VALUE  ID2   NAME2   VALUE2
                      5     ghi     30
                      6     gkl     40

If Table_1 has no records. Same is true for Table_2. I tried to use cross join, but then the data will repeat.

Thanks a lot

Upvotes: 2

Views: 2340

Answers (3)

RickyRam
RickyRam

Reputation: 181

This also works

select * from Table_1,Table_2

Upvotes: 0

neshkeev
neshkeev

Reputation: 6476

Try this:

with Table_1(ID, NAME, VALUE) as (
  select 1, 'abc', 10 union all
  select 2, 'def', 20 union all
  select 3, 'def', 20
), Table_2(ID2, NAME2, VALUE2) as (
  select 5, 'ghi', 30 union all
  select 6, 'gkl', 40
), prep_table_1 (ID, NAME, VALUE, rn) as (
  select id, name, value, row_number() over(order by id)
    from table_1
), prep_table_2 (ID2, NAME2, VALUE2, rn) as (
  select id2, name2, value2, row_number() over(order by id2)
    from table_2
)
select t1.ID, t1.NAME, t1.VALUE, t2.ID2, t2.NAME2, t2.VALUE2
  from prep_table_1 t1
  full outer join prep_table_2 t2 on t1.rn = t2.rn

SQLFiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You need to add a join condition. In this case, by using row_number() to add a sequential number on each side. Then full outer join to get all the records:

select t1.id, t1.name, t1.value, t2.id as id2, t2.name as name2, t2.value as value2
from (select t1.*, row_number() over (order by id) as seqnum
      from table_1 t1
     ) t1 full outer join
     (select t2.*, row_number() over (order by id) as seqnum
      from table_2 t2
     ) t2
     on t1.seqnum = t2.seqnum;

Upvotes: 3

Related Questions