Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to join multiple tables to show all Exceptions but avoid cartesian product Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 8:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 8:34 PM
Points: 6, Visits: 15
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.TradeId AND 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

Post #1476614
Posted Tuesday, July 23, 2013 9:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 13,067, Visits: 11,906
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1476677
Posted Wednesday, July 24, 2013 3:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:42 PM
Points: 29, Visits: 453
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

Post #1477288
Posted Tuesday, July 30, 2013 11:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 8:34 PM
Points: 6, Visits: 15
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!
Post #1479111
Posted Tuesday, July 30, 2013 11:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 8:34 PM
Points: 6, Visits: 15
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!
Post #1479112
Posted Tuesday, July 30, 2013 2:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:42 PM
Points: 29, Visits: 453
Glad it helped
Post #1479164
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse