InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 04:29:21 GMT20http://www.sqlservercentral.com/Forums/Topic942260-392-1.aspx[code="sql"]SELECT CT.Contact_IDFROM ContactTracking CTWHERE CT.contact_date > '20090110'AND CT.Call_end_time IS NOT NULLAND Contact_ID IN ('122734', '122738')AND EXISTS ( SELECT * FROM HPS_COMMON.dbo.wrk_user WU JOIN HPS_COMMON.dbo.def_Usertype UT ON UT.id_UserType = WU.id_UserType WHERE WU.id_User = CT.UserID );[/code]Thu, 24 Jun 2010 04:31:47 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic942260-392-1.aspx[quote][b]Vinay Theethira (6/24/2010)[/b][hr]Here is my SQL Query, This query should not fetch duplicate contactID(i.e Same ContactID).but when i execute the query i get duplicated ContactID. Could anyone tell me what is wrong with my Query,Here is my queryselect ContactTracking.Contact_IDFROMContactTracking ContactTrackingLEFT OUTER JOIN Issues Issues ON ContactTracking.Issue_ID = Issues.Issue_IDLEFT OUTER JOIN HRA hra ON hra.HcnID = ContactTracking.HcnIDINNER JOIN HPS_COMMON.dbo.wrk_user wrk_User ON ContactTracking.UserID = wrk_User.id_UserINNER JOIN HPS_COMMON.dbo.def_Usertype def_Usertype ON wrk_User.id_UserType = def_Usertype.id_UserTypeWHEREContactTracking.contact_date>'10-jan-2009'AND ContactTracking.Call_end_time is not nullAND Contact_ID in ('122734','122738')And here is the ContactId result i get,122734122734122738122738Expected Result is,122734122738So i thought Group By will solve the iss[/quote]Where exactly in your query you have put "GROUP BY ContactTracking.ContactId", I cannot see it, must be very well hidden. :-DThu, 24 Jun 2010 03:49:20 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic942260-392-1.aspxProbably cardinality changes in your joins to other tables. I can't tell from here.Try using DISTINCT in your query (it goes immediately after SELECT).I would suggest you to check the relationships between your tables and find out if duplicating rows in joins is permitted by the database schema or is due to a wrong query.Thu, 24 Jun 2010 01:52:11 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic942260-392-1.aspxHere is my SQL Query, This query should not fetch duplicate contactID(i.e Same ContactID).but when i execute the query i get duplicated ContactID. Could anyone tell me what is wrong with my Query,Here is my queryselect ContactTracking.Contact_IDFROMContactTracking ContactTrackingLEFT OUTER JOIN Issues Issues ON ContactTracking.Issue_ID = Issues.Issue_IDLEFT OUTER JOIN HRA hra ON hra.HcnID = ContactTracking.HcnIDINNER JOIN HPS_COMMON.dbo.wrk_user wrk_User ON ContactTracking.UserID = wrk_User.id_UserINNER JOIN HPS_COMMON.dbo.def_Usertype def_Usertype ON wrk_User.id_UserType = def_Usertype.id_UserTypeWHEREContactTracking.contact_date>'10-jan-2009'AND ContactTracking.Call_end_time is not nullAND Contact_ID in ('122734','122738')And here is the ContactId result i get,122734122734122738122738Expected Result is,122734122738So i thought Group By will solve the issThu, 24 Jun 2010 01:41:29 GMTVinay Theethirahttp://www.sqlservercentral.com/Forums/Topic942260-392-1.aspxNo, it can't be done.Depending on the aggregate you're using, you could rewrite the query to achieve the same thing in a different way.Problem is I don't see any aggregate in you query.Why are you grouping if you don't use aggregates?Can you explain the requirement a bit further?Thu, 24 Jun 2010 01:33:31 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic942260-392-1.aspxHi,if we are trying to add the several columns in select statement and only one condition in group by.how can we do that ??Example:[b]select ContactTracking.Contact_ID,ContactTracking.HcnIDFROM ContactTracking ContactTracking LEFT OUTER JOIN HRA hra ON hra.HcnID = ContactTracking.HcnID WHERE ContactTracking.contact_date>'10-jan-2009' AND ContactTracking.Call_end_time is not null AND ContactTracking.HcnID NOT IN (Select HcnID FROM TestIDs) AND Contact_ID in ('122734','122738') GROUP BY Contact_ID[/b]Thu, 24 Jun 2010 01:13:03 GMTVinay Theethira