InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 07:17:22 GMT20http://www.sqlservercentral.com/Forums/Topic845205-145-1.aspxTry this:[code="sql"] 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[/code]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.Mon, 11 Jan 2010 04:10:48 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic845205-145-1.aspxi didnt get ur question but if u e asking about which query is corect and the query which has UNION ALLMon, 11 Jan 2010 03:32:14 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic845205-145-1.aspx[quote][b]Bhuvnesh (1/11/2010)[/b][hr]sample data is not possible , as it includes amny tables[b]yes row count is correct[/b] and time taken = 18 secs but my concerns is UNION ALL with same tables i want to replace it with correlated query.[/quote][quote][b]Bhuvnesh (1/11/2010)[/b][hr]yes . [b]ur query is not returning any row[/b][/quote]So...which is it?Mon, 11 Jan 2010 03:29:12 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic845205-145-1.aspxNot working :(Mon, 11 Jan 2010 03:26:19 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic845205-145-1.aspxHi 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 = 2000022Please let me know if this helps....:)Mon, 11 Jan 2010 03:20:42 GMTAzzuhttp://www.sqlservercentral.com/Forums/Topic845205-145-1.aspxyes . ur query is not returning any rowMon, 11 Jan 2010 03:12:56 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic845205-145-1.aspx[quote][b]Bhuvnesh (1/11/2010)[/b][hr]sample data is not possible , as it includes amny tablesyes 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.[/quote]The row count is correct: are there any differences between the results of your first query and my query?Mon, 11 Jan 2010 03:11:23 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic845205-145-1.aspxsample data is not possible , as it includes amny tablesyes 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.Mon, 11 Jan 2010 03:05:18 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic845205-145-1.aspx[quote][b]Bhuvnesh (1/11/2010)[/b][hr]thanks for ur reply but its not working[/quote]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?Mon, 11 Jan 2010 03:01:19 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic845205-145-1.aspxthanks for ur reply but its not workingMon, 11 Jan 2010 02:56:11 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic845205-145-1.aspxNo problem. Note - this is UNTESTED.[code="sql"] 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[/code]Mon, 11 Jan 2010 02:41:46 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic845205-145-1.aspxhiin 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.Mon, 11 Jan 2010 00:54:46 GMTBhuvnesh