CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
user3619447
user3619447

Reputation: 99

SQL Joins Please help me the below

I have data In two tables like below:

enter image description here

I need the third table output. I have used joins but it is not working. I have used rank also.

SELECT 
   a.requestinstancelogid AS requestinstancelogidIn, 
   a.requestinstanceid AS InRequestInstance, 
   a.requeststatuscdid AS Inrequeststatuscdid, 
   a.Addnldocsin, 
   b.requestinstancelogid AS requestinstancelogidOut, 
   b.requestinstanceid AS OutRequestInstance, 
   b.requeststatuscdid AS Outrequeststatuscdid, 
   b.AddnldocsOut, 
   RANK() OVER (PARTITION BY a.requestinstanceid ORDER BY a.requestinstancelogid) AS one, 
   RANK() OVER (PARTITION BY b.requestinstanceid ORDER BY b.requestinstancelogid) AS two
INTO 
   #tmp_MultipleAddnlDocsLender
FROM 
   #tmp_addnldocsin a
LEFT JOIN 
   #tmp_addnldocsout b ON a.requestinstanceid = b.requestinstanceid
WHERE 
   a.requestinstanceid = 164944

This query returns 6 records.

Upvotes: 0

Views: 72

Answers (4)

OttO
OttO

Reputation: 419

I think you need a cross/outer apply like this 

SELECT    
    a.requestinstancelogid AS requestinstancelogidIn, 
    a.requestinstanceid AS InRequestInstance, 
    a.requeststatuscdid AS Inrequeststatuscdid,
    a.Addnldocsin, 
    q.requestinstancelogid AS requestinstancelogidOut, 
    q.requestinstanceid AS OutRequestInstance, 
    q.requeststatuscdid AS Outrequeststatuscdid, 
    q.AddnldocsOut 
FROM #tmp_addnldocsin a
OUTER APPLY (SELECT TOP 1 
    b.requestinstancelogid AS requestinstancelogidOut, 
    b.requestinstanceid AS OutRequestInstance, 
    b.requeststatuscdid AS Outrequeststatuscdid, 
    b.AddnldocsOut 
    FROM #tmp_addnldocsout b 
    WHERE a.requestinstanceidin = b.requestinstanceidout 
    ORDER BY AddnIdocsOut DESC) q
WHERE 
   a.requestinstanceid = 164944

Upvotes: 0

Katherine Elizabeth Lightsey
Katherine Elizabeth Lightsey

Reputation: 853

I believe this is what you're looking for:

declare @request_instance_log_id_in table (
 [log]       [int]
 , [in]      [int]
 , [status]  [int]
 , [created] [datetime] default current_timestamp);
insert into @request_instance_log_id_in
        ([log],[in],[status])
values      (424593,164944,110),
        (424594,164944,110),
        (424595,164944,110);
declare @request_instance_log_id_out table (
[log]       [int]
, [out]     [int]
, [status]  [int]
, [created] [datetime] default current_timestamp);
insert into @request_instance_log_id_out
        ([log],[out],[status])
values      (424593,164944,112),
        (424594,164944,112);
select [request_instance_log_id_in].[log]        as [request_instance_log_id_in_log]
   , [request_instance_log_id_in].[in]       as [request_instance_log_id_in_in]
   , [request_instance_log_id_in].[status]   as [request_instance_log_id_in_status]
   , [request_instance_log_id_in].[created]  as [request_instance_log_id_in_created]
   , [request_instance_log_id_out].[log]     as [request_instance_log_id_out_log]
   , [request_instance_log_id_out].[out]     as [request_instance_log_id_out_out]
   , [request_instance_log_id_out].[status]  as [request_instance_log_id_out_status]
   , [request_instance_log_id_out].[created] as [request_instance_log_id_out_created]
from   @request_instance_log_id_in as [request_instance_log_id_in]
   left join @request_instance_log_id_out as [request_instance_log_id_out]
          on [request_instance_log_id_out].[log] = [request_instance_log_id_in].[log];

request_instance_log_id_in_log request_instance_log_id_in_in request_instance_log_id_in_status request_instance_log_id_in_created request_instance_log_id_out_log request_instance_log_id_out_out request_instance_log_id_out_status request_instance_log_id_out_created 424593 164944 110 2014-09-02 08:55:01.107 424593 164944 112 2014-09-02 08:55:01.107 424594 164944 110 2014-09-02 08:55:01.107 424594 164944 112 2014-09-02 08:55:01.107 424595 164944 110 2014-09-02 08:55:01.107 NULL NULL NULL NULL

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

The only way i can see that you could produce these results is the following:

;WITH Table1CTE AS
(
    SELECT *,
           ROW_NUMBER()OVER(PARTITION BY requestinstanceidin ORDER BY requestinstancelogid) AS rn
    FROM   Table1
), Table2CTE AS
(
    SELECT *,
           ROW_NUMBER()OVER(PARTITION BY requestinstanceidout ORDER BY requestinstancelogid) AS rn
    FROM   Table2
)
SELECT * 
FROM   Table1CTE T1
       LEFT JOIN Table2CTE T2
           ON T1.requestinstanceidin = T2.requestinstanceidout 
              AND T1.rn= T2.rn

Upvotes: 2

Alex Szabo
Alex Szabo

Reputation: 3276

enter image description here

As far as I can tell, the same column in both is the requestinstanceidout (could hardly read it, please post text or code next time)

Based on the idea of the above is correct, you could have something like this:

SELECT 
*
FROM 
table1 a
LEFT JOIN table 2 b
ON a.requestinstanceidin = b.requestinstanceidout

Hope this will help you!

Upvotes: 1

Related Questions