umair yasin umair yasin - 7 months ago 9
SQL Question

How to select only distinct result based on particular criteria from tables using join

I have two tables both contains "callid,callnumber,callduration"..now i want to get a call which have same callnumber whereas callduration is greater than a given value.i.e

Select a.callnumber
, b.callnumber
from table a
INNER JOIN table b ON ( a.callnumber = b.callnumber
AND a.callduration-b.callduration > value );


but this returns multiple results fulfilling the creteria whereas i just want a call which have minimum duration difference.

Answer

the join condition in your original sql and your verbal explanation mismatch, so here are two versions: with the durations above a given value as opposed to their difference being greater than the given value:

v1:

    Select a.callnumber
         , min (a.callduration-b.callduration)  mindiff
      from table a
INNER JOIN table b ON (    a.callnumber = b.callnumber 
                       AND a.callduration > value         
                       AND b.callduration > value        )
  group by a.callnumber
         ; 

v2:

    Select a.callnumber
         , min (a.callduration-b.callduration)  mindiff
      from table a
INNER JOIN table b ON (    a.callnumber = b.callnumber 
                       AND a.callduration-b.callduration > value )
  group by a.callnumber
         ; 
Comments