asked
tricky query select with exist
Hi, Do you know what's wrong with my query? I've an error on the exist. SELECT CRMAF_FilteredContact.OwnerID ,CRMAF_FilteredContact.OwnerIDname ,SU.businessunitidname ,SU.eu_reporthubname -- ,SU.eu_reportcountryidname ,CRMAF_FilteredContact.ContactID ,CRMAF_FilteredContact.FullName ,CRMAF_FilteredContact.invoke_tiername ,CRMAF_FilteredAppointment.ActivityID ,CRMAF_FilteredAppointment.RegardingObjectID ,CRMAF_FilteredAppointment.Subject FROM FilteredContact CRMAF_FilteredContact JOIN FilteredSystemUser SU ON CRMAF_FilteredContact.ownerid = SU.SystemUserID LEFT JOIN FilteredAppointment CRMAF_FilteredAppointment ON SU.SystemUserID = CRMAF_FilteredAppointment.ownerid AND CRMAF_FilteredAppointment.activityid EXISTS ( SELECT CRMAF_FilteredAppointment.activityid, CRMAF_FilteredAppointment.subject, count(AP.partyid) AS qte FROM FilteredAppointment AS CRMAF_FilteredAppointment RIGHT OUTER JOIN FilteredActivityParty AS AP ON CRMAF_FilteredAppointment.activityid = AP.activityid RIGHT OUTER JOIN FilteredSystemUser AS SU ON CRMAF_FilteredAppointment.ownerid = SU.systemuserid AND (AP.participationtypemask = 8 or AP.participationtypemask =5) WHERE (SU.eu_securityrole LIKE '%RAM%') AND (CRMAF_FilteredAppointment.owneridname LIKE '%myName%') GROUP BY CRMAF_FilteredAppointment.activityid, CRMAF_FilteredAppointment.subject) WHERE SU.eu_securityrole like '%RAM%' and (CRMAF_FilteredContact.owneridname LIKE '%myName%') ORDER BY CRMAF_FilteredContact.OwnerIDname Below some information about tables: I've four table. - SystemUser (systemUserId) - FilteredContact (onwerid, owneridname, contactid, lastname..) - FilteredAppointment (ownerid, stateCode, Subject, ActivityId, CreatedBy, OwnerIdName..) - FilteredActivityParty (ActivityId, PartyId, participationTypeMask...) So basicly the user can create contact, so it becomes the owner of the contact. And user can create appointments, but need to choose a contact as required to the appointment. This contact can be created by the current user or not. So FilteredContact.contactID = FilteredActivityParty.partyId , as a contact is in a list of activityParty. FilteredAppointment.activityId = FilteredActivityParty.activityId So FilteredSystemUser.systemUserId = FilteredContact.ownerId FilteredAppointment.activity.ownerId = FilteredContact.contactId With all that, I need to get by FilteredSystemUser.systemUserId, the amount of FilteredContact that Im the owner and I need to know how many people(partyid) where Filteredappointment.ownerId=Filteredsystemuser.systemuserid. And I cant find a way to manage this without two datasets. I've no idea how to link thus two datasets between them. Thanks for your help
Have you tried to move the exist clause to the WHERE clause instead of the JOIN clause? SELECT CRMAF_FilteredContact.OwnerID ,CRMAF_FilteredContact.OwnerIDname ,SU.businessunitidname ,SU.eu_reporthubname -- ,SU.eu_reportcountryidname ,CRMAF_FilteredContact.ContactID ,CRMAF_FilteredContact.FullName ,CRMAF_FilteredContact.invoke_tiername ,CRMAF_FilteredAppointment.ActivityID ,CRMAF_FilteredAppointment.RegardingObjectID ,CRMAF_FilteredAppointment.Subject FROM FilteredContact CRMAF_FilteredContact JOIN FilteredSystemUser SU ON CRMAF_FilteredContact.ownerid = SU.SystemUserID LEFT JOIN FilteredAppointment CRMAF_FilteredAppointment ON SU.SystemUserID = CRMAF_FilteredAppointment.ownerid WHERE SU.eu_securityrole like '%RAM%' and (CRMAF_FilteredContact.owneridname LIKE '%myName%') AND ( CRMAF_FilteredAppointment.activityid IS NULL OR CRMAF_FilteredAppointment.activityid EXISTS ( SELECT CRMAF_FilteredAppointment.activityid, CRMAF_FilteredAppointment.subject, count(AP.partyid) AS qte FROM FilteredAppointment AS CRMAF_FilteredAppointment RIGHT OUTER JOIN FilteredActivityParty AS AP ON CRMAF_FilteredAppointment.activityid = AP.activityid RIGHT OUTER JOIN FilteredSystemUser AS SU ON CRMAF_FilteredAppointment.ownerid = SU.systemuserid AND (AP.participationtypemask = 8 or AP.participationtypemask =5) WHERE (SU.eu_securityrole LIKE '%RAM%') AND (CRMAF_FilteredAppointment.owneridname LIKE '%myName%') GROUP BY CRMAF_FilteredAppointment.activityid, CRMAF_FilteredAppointment.subject)) ORDER BY CRMAF_FilteredContact.OwnerIDname Please note that I added (CRMAF_FilteredAppointment.activityid IS NULL OR before your EXISTS CRMAF_FilteredAppointment.activityid and a ). If this doesn't work try to use an alias for your EXISTS clause. Another recommendation is to create a temp table to reduce your complex joins or att some common table expressions (SQL server 2005 or later)
Maybe, I have only focused on the T-SQL logic to find the problem with EXISTS and have not even tried to understand the underlying design or purpose. I don't think KISS has been implemented in this code. :)
One problem is that you code looks like this .... AND CRMAF_FilteredAppointment.activityid EXISTS (...) ..... that isn't the right use of [EXISTS][1], so either remove the column name before it OR change it to IN and only return one column from the sub-query - depending on what you are trying to check for [1]:
http://msdn.microsoft.com/en-us/library/ms188336.aspx
