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

  • 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

  • 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/

  • 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

  • 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!

  • 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!

  • 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