CODEWITHSUNDEEP
sqlsql-server
user4370900
user4370900

Reputation:

SQL Server : connect two results with specific row

I have a table of users and a table of their contracts. I have two selects. The first one, which selects all from users table and second one, which selects only new users (from another user table) for "today". I need to connect those two results, but there's a problem.

Each user had "rownumber" column, it depends by count of contracts for each user (each user can have 1 or more contracts). I need to get new rownumber for new row. See example

The first select returns:

user_id | contract_id | rownumber
1         456           1
1         457           2
1         699           3

The second select selects new rows for those users:

user_id | contract_id
1         1024
1       | 1079

And I need to connect those results to get this end result:

user_id | contract_id | rownumber
 1         456           1
 1         457           2
 1         699           3
 1         1024          4
 1         1079          5

How can I do that?

Upvotes: 3

Views: 56

Answers (2)

Sindhu
Sindhu

Reputation: 416

select USER_ID, CONTRACT_ID,ROWNUMBER
from TABLE1 
union all
select USER_ID, CONTRACT_ID,rownum+num
from TABLE2,(select max(ROWNUMBER) as num
from TABLE1);

union all will display duplicate datas also. If you don't want duplicate records then replace union all by union

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28900

;with cte
as
(
select 
user_id,contractid from table1
union all
select 
user_id,contractid from table2
)
select *,row_number() over (partition by user_id order by contractid ) from cte

Upvotes: 2

Related Questions