July 23, 2013 at 8:32 am
Hi all,
Ive got the following SQL query which takes all the trades from the trade table and joins a number of different tables to this trade table if it can join on tradeId. Now the other tables (TC, TA and ET) each may have multiple instances of the same tradeId but I dont want to join every instance to each other.
For example the trade table will only have unique trade ids and the TC table might have 5 rows of the same trade Id and the TA might have 11 rows (and for simplicity lets say the ET table has 0). I want to pull back only 16 rows of the this trade to show that it has been found 16 different times across the 3 tables.
My current query pulls back 55 rows (5 x 11) as it is joining each row
Hope this makes sense, Im still a newbie to SQL. Would massively appreciate any help!!
Thanks
WITH
TC As
(select TC.TradeCommentId, TC.TradeId, TC.TradeCommentEnteredUser, TC.TradeCommentDate, TC.TradeComment,
ROW_NUMBER() OVER (PARTITION BY TC.TradeId ORDER BY TC.TradeCommentDate DESC) As TCRow
From TradeComment TC),
TA As
(select TA.TradeId, TA.TradeAuditUserName, TA.TradeAuditAction, TA.TradeAuditVersion,
ROW_NUMBER() OVER (PARTITION BY TA.TradeId ORDER BY TA.TradeAuditVersion DESC) As TARow
From TradeAudit TA),
ET As
(select ET.TaskId, ET.TradeId, ET.TaskOwner, ET.TaskNewDate, ET.EventType, ET.EventTypeStatus,
ROW_NUMBER() OVER (PARTITION BY ET.TradeId ORDER BY ET.TaskNewDate DESC) As ETRow
From EventTask ET)
SELECT
T.TradeId,
T.TradeDate,
T.TradeSettlementDate,
T.ProductFamily,
T.TradeStatus,
LE.OperationGroup,
LE.POId,
LE.ShortName,
LE.LegalEntityRole,
TC.TradeId As 'TC TradeId',
TC.TradeCommentId,
TCRow,
TC.TradeCommentEnteredUser,
TC.TradeComment,
TA.TradeId As 'TA TradeId',
TA.TradeAuditUserName,
TA.TradeAuditAction,
TARow,
ET.TradeId As 'ET TradeId',
ET.TaskId,
ET.TaskOwner,
ET.EventType,
ET.EventTypeStatus,
ETRow,
case when
(ET.TradeId IS NOT NULL OR TC.TradeId IS NOT NULL OR TA.TradeID IS NOT NULL)
Then 'Exception' Else 'Straight Through' End As 'Exception'
FROM
Trade T
left join LegalEntity LE on (LE.LegalEntityId = T.CounterpartyId AND LE.POId = T.POId)
left join TC on (TC.TradeId = T.TradeIdAND TC.TradeCommentEnteredUser <> 'otpcalsys')
left join TA on (TA.TradeId = T.TradeId AND TA.TradeAuditUserName <> 'otpcalsys')
left join ET on (ET.TradeId = T.TradeId)
WHERE
(ET.TradeId IS NOT NULL OR TC.TradeId IS NOT NULL OR TA.TradeID IS NOT NULL)
AND T.TradeSettlementDate = '05 Jul 2013'
AND LE.LegalEntityRole = 'Counterparty'
ORDER BY 1
July 23, 2013 at 9:42 am
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 24, 2013 at 3:14 pm
I'll admit I may be ignoring some of the details, but I think the essence of the question is how to avoid a cartesian product when left joining to multiple tables. From your description, it seems that you want your result to really be more like a union of results from several joins.
Here's an example that I think shows a pattern you could follow, however you would have to be CAREFUL TO CHECK PERFORMANCE characteristics of the join to the Combi union.
with
Trade(TradeId) as (
select 1
union
select 2
union
select 3
),
TC(TradeId, TradeComment) as (
select 1 , 'TC 1a'
union
select 1 , 'TC 1b'
union
select 1 , 'TC 1c'
union
select 2 , 'TC 2a'
),
TA(TradeId, TradeAuditAction) as (
select 1 , 'TA 1a'
union
select 1 , 'TA 1b'
),
Combi(TradeId, TradeComment, TradeAuditAction) as(
select TradeId, TradeComment , null
from TC
union all
select TradeId, null , TradeAuditAction
from TA
)
select Trade.TradeId, Combi.TradeComment, Combi.TradeAuditAction
from Trade
left join Combi on Combi.TradeId = Trade.TradeId
It's not clear whether you really want a row in the result set for a Trade that has no matching rows in the three tables, but my example above assumes that you do. The answer would actually be simpler if you do not want such a row.. something like ....
-- with Trade, TC, TA as above
select Trade.TradeId, TC.TradeComment, null as TradeAuditAction
from Trade
join TC on TC.TradeId = Trade.TradeId
union all
select Trade.TradeId, null, TA.TradeAuditAction
from Trade
join TA on TA.TradeId = Trade.TradeId
July 30, 2013 at 11:17 am
Mate that's exactly what I needed! I had to adjust slightly but in essence I just created a union between the different results. I was worried it would run really slowly but it was surprisingly fast!
July 30, 2013 at 11:18 am
Mate that's exactly what I needed! I had to adjust slightly but in essence I just created a union between the different results. I was worried it would run really slowly but it was surprisingly fast!
July 30, 2013 at 2:44 pm
Glad it helped
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply