Query difference: HOw to Avoiding UNION ALL with correlated Query

  • hi

    in my first query i have a UNION ALL operator, i want to avoid it as it contains most of the table repetitive , so there is only two difference in query ( who have UNION ALL in between ) , i marked those chances with ( ------> difference ) sign

    i have also added "my query" in which i have used correlated query but its not working as expected.

    i am attaching both New and Old Query in Zip file

    i am soory to tell you that i cant put table and index schema

    So please try to do it without them.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • No problem. Note - this is UNTESTED.

    select

    reg_id = ISNULL(dr1.reg_id, dr2.reg_id), -- changed

    invitee_id = ISNULL(di1.invitee_id, di2.invitee_id), -- changed

    contact_type = isnull(ct.cont_type_name, ''),

    item_id = I.item_id,

    item_type = i.item_type,

    item_category = I.item_category,

    item_name = I.item_name,

    item_code = I.item_code,

    fee_name = f.fee_name,

    start_date = I.session_start_date,

    end_date = I.session_end_date,

    reg_date = ae.created_date,

    item_quantity = ae.prod_qty,

    item_price = bd.bskt_dtl_price_tier_amt,

    eligible_credit = I.credit_value,

    participant_flag = case

    when pe.acct_id is null then 0

    else 1 end,

    cv_acct_id = ae.acct_id,

    cv_entity_stub = ae.entity_stub,

    cv_entity_type_id = ae.entity_type_id,

    cv_prod_stub = ae.prod_stub

    from [dbo].[ATTENDEE_ENTITY] ae with (nolock)

    join [dbo].[DMP_EVENT_ITEM] I with (nolock)

    on I.cv_acct_id = ae.acct_id and I.cv_evt_stub = ae.evt_stub

    AND ae.prod_stub = I.cv_prod_stub

    left join [dbo].[CONTACT_TYPE] ct with (nolock)

    on ct.acct_id = ae.acct_id and ct.cont_type_stub = ae.cont_type_stub

    left join [dbo].[BASKET_DETAIL] bd with (nolock)

    on bd.acct_id = ae.acct_id and bd.bskt_dtl_stub = ae.bskt_dtl_stub

    left join [dbo].[FEE] f with (nolock)

    on f.acct_id = bd.acct_id and f.fee_stub = bd.bskt_dtl_fee_stub

    -- changes

    join [dbo].[DMP_EVENT_INVITEE] di1 with (nolock)

    on di.cv_acct_id = ae.acct_id and di.cv_cont_stub = ae.entity_stub -- > Difference

    and di.cv_free_stub = '00000000-0000-0000-0000-000000000000'

    and di.cv_evt_stub = i.cv_evt_stub

    join [dbo].[DMP_EVENT_REGISTRATION] dr1 (nolock)

    on dr.invitee_id = di1.invitee_id and dr.event_id = di1.event_id

    LEFT join [dbo].[DMP_EVENT_INVITEE] di2 with (nolock)

    on di.cv_acct_id = ae.acct_id and di.cv_free_stub = ae.entity_stub -- > Difference

    join [dbo].[DMP_EVENT_REGISTRATION] dr2 with (nolock)

    on dr2.cv_invitee_stub = di2.cv_invitee_stub and dr2.event_id = di2.event_id

    -- \changes

    left join [dbo].[PARTICIPANT_ENTITY] pe (nolock)

    on pe.acct_id = ae.acct_id

    and pe.evt_stub = ae.evt_stub and pe.prod_stub = i.cv_prod_stub and pe.entity_stub = ae.entity_stub

    where ae.acct_id = 2000022

    AND (ae.entity_type_id = 3 OR ae.entity_type_id = 10) -- > Changes

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • thanks for ur reply but its not working

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (1/11/2010)


    thanks for ur reply but its not working

    There's always a danger of this when there's no sample data to test against. Could you provide some details of why it's not working?

    Does it fail with an error?

    Does it provide the correct row count?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • sample data is not possible , as it includes amny tables

    yes row count is correct and time taken = 18 secs

    but my concerns is UNION ALL with same tables

    i want to replace it with correlated query.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (1/11/2010)


    sample data is not possible , as it includes amny tables

    yes row count is correct and time taken = 18 secs

    but my concerns is UNION ALL with same tables

    i want to replace it with correlated query.

    The row count is correct: are there any differences between the results of your first query and my query?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • yes . ur query is not returning any row

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Hi Bhuvnesh

    select

    reg_id = dr.reg_id,

    invitee_id = di.invitee_id,

    contact_type = isnull(ct.cont_type_name, ''),

    item_id = I.item_id,

    item_type = i.item_type,

    item_category = I.item_category,

    item_name = I.item_name,

    item_code = I.item_code,

    fee_name = f.fee_name,

    start_date = I.session_start_date,

    end_date = I.session_end_date,

    reg_date = ae.created_date,

    item_quantity = ae.prod_qty,

    item_price = bd.bskt_dtl_price_tier_amt,

    eligible_credit = I.credit_value,

    participant_flag = case

    when pe.acct_id is null then 0

    else 1 end,

    cv_acct_id = ae.acct_id,

    cv_entity_stub = ae.entity_stub,

    cv_entity_type_id = ae.entity_type_id,

    cv_prod_stub = ae.prod_stub

    from [dbo].[ATTENDEE_ENTITY] ae with (nolock)

    join [dbo].[DMP_EVENT_ITEM] I with (nolock)

    on I.cv_acct_id = ae.acct_id and I.cv_evt_stub = ae.evt_stub

    AND ae.prod_stub = I.cv_prod_stub

    left join [dbo].[CONTACT_TYPE] ct with (nolock)

    on ct.acct_id = ae.acct_id and ct.cont_type_stub = ae.cont_type_stub

    left join [dbo].[BASKET_DETAIL] bd with (nolock)

    on bd.acct_id = ae.acct_id and bd.bskt_dtl_stub = ae.bskt_dtl_stub

    left join [dbo].[FEE] f with (nolock)

    on f.acct_id = bd.acct_id and f.fee_stub = bd.bskt_dtl_fee_stub

    join [dbo].[DMP_EVENT_INVITEE] di with (nolock)

    on di.cv_acct_id = ae.acct_id

    --- Changes

    and Case When ISNULL(cv_cont_stub,0) != 0 THEN di.cv_cont_stub ELSE di.cv_free_stub END = ae.entity_stub

    --and di.cv_free_stub = '00000000-0000-0000-0000-000000000000'

    --- /Changes

    and di.cv_evt_stub = i.cv_evt_stub

    join [dbo].[DMP_EVENT_REGISTRATION] dr (nolock)

    on dr.invitee_id = di.invitee_id and dr.event_id = di.event_id

    left join [dbo].[PARTICIPANT_ENTITY] pe (nolock)

    on pe.acct_id = ae.acct_id

    and pe.evt_stub = ae.evt_stub and pe.prod_stub = i.cv_prod_stub and pe.entity_stub = ae.entity_stub

    where

    --- Change

    ae.entity_type_id = Case When ISNULL(cv_cont_stub,0) != 0 THEN 3 ELSE 10 END

    ---/Change

    AND ae.acct_id = 2000022

    Please let me know if this helps....:)

  • Not working 🙁

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (1/11/2010)


    sample data is not possible , as it includes amny tables

    yes row count is correct and time taken = 18 secs

    but my concerns is UNION ALL with same tables

    i want to replace it with correlated query.

    Bhuvnesh (1/11/2010)


    yes . ur query is not returning any row

    So...which is it?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • i didnt get ur question but if u e asking about which query is corect and the query which has UNION ALL

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Try this:

    select

    reg_id = ISNULL(dr1.reg_id, dr2.reg_id), -- changed

    invitee_id = ISNULL(di1.invitee_id, di2.invitee_id), -- changed

    contact_type = isnull(ct.cont_type_name, ''),

    item_id = I.item_id,

    item_type = i.item_type,

    item_category = I.item_category,

    item_name = I.item_name,

    item_code = I.item_code,

    fee_name = f.fee_name,

    start_date = I.session_start_date,

    end_date = I.session_end_date,

    reg_date = ae.created_date,

    item_quantity = ae.prod_qty,

    item_price = bd.bskt_dtl_price_tier_amt,

    eligible_credit = I.credit_value,

    participant_flag = case

    when pe.acct_id is null then 0

    else 1 end,

    cv_acct_id = ae.acct_id,

    cv_entity_stub = ae.entity_stub,

    cv_entity_type_id = ae.entity_type_id,

    cv_prod_stub = ae.prod_stub

    from [dbo].[ATTENDEE_ENTITY] ae with (nolock)

    join [dbo].[DMP_EVENT_ITEM] I with (nolock)

    on I.cv_acct_id = ae.acct_id and I.cv_evt_stub = ae.evt_stub

    AND ae.prod_stub = I.cv_prod_stub

    left join [dbo].[CONTACT_TYPE] ct with (nolock)

    on ct.acct_id = ae.acct_id and ct.cont_type_stub = ae.cont_type_stub

    left join [dbo].[BASKET_DETAIL] bd with (nolock)

    on bd.acct_id = ae.acct_id and bd.bskt_dtl_stub = ae.bskt_dtl_stub

    left join [dbo].[FEE] f with (nolock)

    on f.acct_id = bd.acct_id and f.fee_stub = bd.bskt_dtl_fee_stub

    -- changes

    LEFT join [dbo].[DMP_EVENT_INVITEE] di1 with (nolock)

    on di.cv_acct_id = ae.acct_id and di.cv_cont_stub = ae.entity_stub -- > Difference

    and di.cv_free_stub = '00000000-0000-0000-0000-000000000000'

    and di.cv_evt_stub = i.cv_evt_stub

    LEFT join [dbo].[DMP_EVENT_REGISTRATION] dr1 (nolock)

    on dr.invitee_id = di1.invitee_id and dr.event_id = di1.event_id

    LEFT join [dbo].[DMP_EVENT_INVITEE] di2 with (nolock)

    on di.cv_acct_id = ae.acct_id and di.cv_free_stub = ae.entity_stub -- > Difference

    LEFT join [dbo].[DMP_EVENT_REGISTRATION] dr2 with (nolock)

    on dr2.cv_invitee_stub = di2.cv_invitee_stub and dr2.event_id = di2.event_id

    -- \changes

    left join [dbo].[PARTICIPANT_ENTITY] pe (nolock)

    on pe.acct_id = ae.acct_id

    and pe.evt_stub = ae.evt_stub and pe.prod_stub = i.cv_prod_stub and pe.entity_stub = ae.entity_stub

    where ae.acct_id = 2000022

    AND (ae.entity_type_id = 3 OR ae.entity_type_id = 10) -- > Changes

    If it doesn't give the results you are expecting, please explain how the results from this query differ from those from your original query with the UNION.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply