CODEWITHSUNDEEP
sqlsql-server
user2961127
user2961127

Reputation: 1143

Comparing SQL query results

I have the following queries.

Query 1:

SELECT sb.number
     , sb.Trace
     , sb.Amount
     , sp.edge, sp.UserId
  FROM Budget sb JOIN SNAP sp ON sb.Trace = sp.Trace
 WHERE sb.Trace 
    IN (SELECT Trace FROM SNAP WHERE User='R5' )
 ORDER BY sp.edge DESC

Output: 

number      Trace       Amount  Edge        UserId
111276509   40902337    30.00   21673074    R5
111276507   40902333    17.00   21673073    R5
111276505   40902331    29.00   21673071    R5

Query 2:

SELECT sb.number
     , sb.Trace
     , sb.Amount
     , sp.edge,sp.UserId  
  FROM Budget sb JOIN SNAP sp ON sb.Trace = sp.Trace
 WHERE sb.Trace 
    IN (SELECT Trace FROM SNAP WHERE UserId<>'R5' )
 ORDER BY sp.edge DESC

Output:

number      Trace       Amount  Edge        UserId
111276509   50902337    20.00   21673074    App
111276507   50902333    50.00   21673073    App
111276505   50902331    70.00   21673071    App

Now, I just need to compare the Edge column from both outputs and if the value is the same it should output corresponding records from both tables. Eg: for Edge value 21673074 I should get

number      Trace       Amount  Edge        UserId
111276509   40902337    30.00   21673074    R5
111276509   50902337    20.00   21673074    App

Upvotes: 2

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

One method uses window functions:

select sbp.*
from (select sb.number, sb.Trace, sb.Amount, sp.edge, sp.UserId,
             sum(case when User = 'R5' then 1 else 0 end) over (partition by trace) as num_R5,
             sum(case when User <> 'R5' then 1 else 0 end) over (partition by trace) as num_NotR5
      from Budget sb join
           SNAP sp 
           on sb.Trace = sp.Trace
     ) sbp
where num_R5 > 0 and num_NotR5 > 0
order by sp.edge desc;

Another just builds on your queries:

select sb.number, sb.Trace, sb.Amount, sp.edge, sp.UserId
from Budget sb join
     SNAP sp 
     on sb.Trace = sp.Trace
where sb.Trace in (select Trace from SNAP where User = 'R5' ) and
      sb.Trace in (select Trace from SNAP where User <> 'R5' )
order by sp.edge desc;

Of course, one of the two exists is redundant, because the current row automatically matches one of them. You can make this more efficient as:

select sb.number, sb.Trace, sb.Amount, sp.edge, sp.UserId
from Budget sb join
     SNAP sp 
     on sb.Trace = sp.Trace
where (sp.User <> 'R5' and
       sb.Trace in (select Trace from SNAP where User = 'R5' )
      ) or
      (sp.User = 'R5' and
       sb.Trace in (select Trace from SNAP where User <> 'R5' )
      )
order by sp.edge desc;

Upvotes: 2

Related Questions