CODEWITHSUNDEEP
sqlsql-serverjoin
srh
srh

Reputation: 1671

Create Query to join 2 tables 1 on 1 with nothing in common

I have a table TAB_1 which has 230 rows.

CREATE TABLE TAB_1 (audit_id bigint NOT NULL PRIMARY KEY)

I have another table TAB_2 which also has 230 rows. 

CREATE TABLE TAB_2 (employee_id bigint NOT NULL PRIMARY KEY, first_name varchar(50) NOT NULL, last_name varchar(50) NOT NULL)

Both these tables have nothing in common.

I want to write a query which will give me all the columns in these 2 tables 1 on 1 (i.e. 1st row of TAB_1 with 1st row of TAB_2, 2nd row of TAB_1 with 2nd row of TAB_2 and so on). That query will also have 230 rows.

How can I do that?

I tried these queries but they are given result as every row in TAB_1 times every row in TAB_2:

select a.audit_id, b.employee_id, b.first_name, b.last_name
from TAB_1 a inner join TAB_2 b on 1 = 1

select a.audit_id, b.employee_id, b.first_name, b.last_name
from TAB_1 a cross join TAB_2 b

Upvotes: 7

Views: 16429

Answers (4)

MikeChainSaw
MikeChainSaw

Reputation: 31

If you can add and extra column on both tables called for example "joinparameter" and have it go from 1 to 230 and join then both by that maybe that can work.

Something like this:

select a.audit_id, b.employee_id, b.first_name, b.last_name from TAB_1 a inner join TAB_2 b on a.joinparameter = b.joinparameter

Or if both audit_id and employee_id go from 1 to 230 you can join them

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You need a key to join on. You can get this using row_number():

select t1.*, t2.*
from (select t1.*, row_number() over (order by audit_id) as seqnum
      from tab_1 t1
     ) t1 full outer join
     (select t2.*, row_number() over (order by employee_id) as seqnum
      from tab_2 t2
     ) t2
     on t1.seqnum = t2.seqnum;

This assumes that the ordering is based on the first column. The full outer join will return all rows, regardless of whether they have the same number of rows or not.

Upvotes: 12

nicomp
nicomp

Reputation: 4657

Create two new queries and join them:

These new queries will have a new column: row_number()

Select *, Row_NUMBER() as rowTab1 from TAB_1

Select *, Row_Number() as rowTab2 from Tab_2

Now create a third query containing the previous two views and join on rowTab1 = rowTab2

Upvotes: 2

Langosta
Langosta

Reputation: 497

You don't want to join on "nothing" because you'll end up with a cartesian join -- and that will give you way more than 230 rows. Instead, you want to join on row number. Here's someone that did it for you already.

Upvotes: 3

Related Questions