question

BugsBunny avatar image
BugsBunny 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
t-sqlexists
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
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)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark avatar image Mark commented ·
@Hakan, I wouldn't think that there would be a need for any RIGHT OUTER JOINS in the SELECT of the EXISTS clause. Couldn't they just be removed?
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
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. :)
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image Håkan Winther commented ·
+1 You are soo right! I was too blind to see that! :)
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
@Håkan - I do it all the time.....
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.