Self Join

[zinkjo]

I have a need to join a table to itself. I need to determine if for a given deal number the swap deal number does not exist on the table. Here is my query thus far, but it is really dogging. Is there a better approach ??

select a.counter_ccy,
a.value_date,
sum(a.counter_amt) * -1
from trade_data a
where a.branch = 'NY' and
a.deal_type = 'SELL' and
not exists (select 1 from trade_data b where b.deal_num = a.swap_deal_num and b.ticket_area = a.ticket_area)
group by a.currency, a.value_date

 

[RileyCat]

First problem is the use of "Not In". Big dog on processing right there. Instead, use left join on null'd records from the sub query.

The best way to learn is by trial and error so see what you can come up with from this suggestion and repost your new query to the forum if you need additional assistance.

Stay Cool Ya'll!

-- Kristin

 

[zinkjo]

not 100% sure what you mean by that...could you post a sample....thanks

 

[john76]

Try this and see if it works:

SELECT Counter_CCY,
       Value_Date,
       Counter_Sum = SUM(Counter_AMT) * -1

  FROM  Trade_Data A

    LEFT JOIN  Trade_Data B
    ON    B.Deal_Num = A.Swap_Deal_Num
      AND B.Ticket_Area = A.Ticket_Area

  WHERE B.Deal_Num IS NULL
    AND B.Ticket_Area IS NULL

    AND A.Branch = 'NY'
    AND A.Deal_Type = 'SELL'

  GROUP BY A.Currency,
           A.Value_Date

Most of the time, a query that uses EXISTS or NOT EXISTS will perform slowly. Try using JOINs such as above and see if it improves the performance.

Alternately, if the tables are large consider your indexing strategy. Properly placed indexes can dramatically improve performance. I'm not an indexing expert by any means but consider indexes on the fields you are joining by (deal_num, swap_deal_num, ticket_area) and the fields in your selection criteria (branch, deal_type)

HTH,
John

 

[john76]

Oh, make the SELECT list:

SELECT [red]A.[/red]Counter_CCY,
       [red]A.[/red]Value_Date,
       [red]A.[/red]Counter_Sum = SUM(Counter_AMT) * -1

John

 

[john76]

Scratch that:

SELECT [red]A.[/red]Counter_CCY,
       [red]A.[/red]Value_Date,
       Counter_Sum = SUM([red]A.[/red]Counter_AMT) * -1

I wish the posts where editable...
John

 

[zinkjo]

I can't read the last 2 posts. Was there an update to the suggested query ?

 

[ESquared]

SELECT A.Counter_CCY,
A.Value_Date,
Counter_Sum = SUM(A.Counter_AMT) * -1

 

[john76]

Just putting the alias "A" in front of the fields in the SELECT list, something I left out of the original query.

John