CODEWITHSUNDEEP
sqlsql-serverquerying
Kev84
Kev84

Reputation: 837

Query get group base on a value

I have the following table (TableOne):

GroupId         TradeId     Status
16848           78          LIVE
16848           79          LIVE
16848           80          LIVE
16848           81          DELETED
16841           82          DELETED
16841           83          LIVE

I need to get the whole group value when given the deleted Trade Id? Is there a simple way to do this? I tried to do some query, but in the end I don't know where to start.

Thanks for the reply guys, maybe i over simplified my problem. Here's more details

The trade Id comes from another mapping table (TableCorrect):

OriginalTradeId     NewTradeId  
100                 81
101                 90
102                 91

I'm given the original TradeId and need to join with "TableCorrect" with "TableOne"

Select * 
from TableOne 
inner join TableCorrect on TableOne.TradeId=TableCorrect.NewTradeID
where TableCorrect.OriginalTradeId={@PassedInValue}

But the above only gives me the deleted entry, but i want the rest of the trades from Table one with the same GroupId

So given OriginalTradeId=100

I want the following output:

   GroupId         TradeId      Status
    16848           78          LIVE
    16848           79          LIVE
    16848           80          LIVE
    16848           81          DELETED

Thanks and Regards,

Kev

Upvotes: 0

Views: 72

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I think a where clause with a subquery might be the simplest solution:

Select t1.*
from TableOne t1
where t1.GroupId = (select top 1 GroupId
                    from TableOne tt1 join
                         TableCorrect tc
                         on tt1.TradeId = tc.NewTraceId
                    where tc.OriginalTradeId = @PassedInValue
                   );

This is not the only way. Another interesting way uses window functions:

select t1.*
from (select t1.*,
             max(case when tc.OriginalTradeId = @PassedInValue then 1 else 0 end) over
                 (partition by t1.GroupId) as HasOriginalTrade
      from TableOne t1 join
           TableCorrect tc
           on t1..TradeId = tc.NewTradeId
     ) t1
where HasOriginalTrade > 0;

Upvotes: 1

Dipon Roy
Dipon Roy

Reputation: 406

DECLARE @myTradeId INT;
SET @myTradeId = 1;

SELECT *
    FROM TableOne
    WHERE GroupId = (SELECT GroupId
                        FROM TableOne
                            WHERE TradeId = @myTradeId)

Upvotes: 0

Related Questions