SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to join multiple tables to show all Exceptions but avoid cartesian product


How to join multiple tables to show all Exceptions but avoid cartesian product

Author
Message
raiyanb
raiyanb
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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


Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25977 Visits: 17526
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.

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)
John Rees
John Rees
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 539
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


raiyanb
raiyanb
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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!
raiyanb
raiyanb
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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!
John Rees
John Rees
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 539
Glad it helped
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search