Monday, March 12, 2012

PLS HELP: Query assistance needed

You need extra brackets in the where clause to properly define the boolean
logic:
WHERE
(dbo.EmailNotificationGroupsRules.RuleId = 2999)
AND
(
(dbo.EmailNotificationGroupsAreas.AreaId = 2)
OR
(dbo.EmailNotificationGroupsAreas.AreaId = 4)
)
Reading your post, I think this is what you intended..
Regards,
Greg Linwood
SQL Server MVP
"VB Programmer" <growNO-SPAM@.go-intech.com> wrote in message
news:e%23sTcx%239DHA.1504@.TK2MSFTNGP12.phx.gbl...
> (Please see the attached gif file for the layout of the "View" in SQL
> Enterprise Manager.)
> I have a query that is returning records, when it shouldn't return
anything.
> The WHERE clause is using 2 conditions:
> 1. EmailNotificationGroupsRules.RuleId = 2999 (which should return NO
> RECORDS) AND
> 2. EmailNotificationGroupsAreas.AreaId = 2 OR 4
> Since the RuleId is invalid the EmailNotifcationGroups table should return
> NO RECORDS. (I did a simple test with just the EmailNotifcationGroups and
> EmailNotifcationGroupsRules table and it did indeed return no records.)
> But, for some reason when in this setup records are returned! Again, I
> would expect no records.
> Any ideas? What am I missing? I want to do this in 1 query.
> Thanks in advance!
> BTW, the FULL SQL statement is this:
> SELECT DISTINCT
> dbo.EmailNotificationContacts.EmailAddress,
> dbo.EmailNotificationGroups.GroupName,
> dbo.EmailNotificationGroups.CustomMessage,
> dbo.EmailNotificationGroups.Description
> FROM dbo.EmailNotificationGroups INNER JOIN
> dbo.EmailNotificationGroupsAreas ON
> dbo.EmailNotificationGroups.GroupId =
> dbo.EmailNotificationGroupsAreas.GroupId
> INNER JOIN
> dbo.EmailNotificationGroupsRules ON
> dbo.EmailNotificationGroups.GroupId =
> dbo.EmailNotificationGroupsRules.GroupId
> INNER JOIN
> dbo.EmailNotificationAssignments ON
> dbo.EmailNotificationGroups.GroupId =
> dbo.EmailNotificationAssignments.GroupId
> INNER JOIN
> dbo.EmailNotificationContacts ON
> dbo.EmailNotificationAssignments.ContactId =
> dbo.EmailNotificationContacts.ContactId
> WHERE (dbo.EmailNotificationGroupsRules.RuleId = 2999) AND
> (dbo.EmailNotificationGroupsAreas.AreaId = 2) OR
> (dbo.EmailNotificationGroupsAreas.AreaId = 4)
>
>Thanks! :-)
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23WciV6%239DHA.1936@.TK2MSFTNGP12.phx.gbl...
> You need extra brackets in the where clause to properly define the boolean
> logic:
> WHERE
> (dbo.EmailNotificationGroupsRules.RuleId = 2999)
> AND
> (
> (dbo.EmailNotificationGroupsAreas.AreaId = 2)
> OR
> (dbo.EmailNotificationGroupsAreas.AreaId = 4)
> )
> Reading your post, I think this is what you intended..
> Regards,
> Greg Linwood
> SQL Server MVP
> "VB Programmer" <growNO-SPAM@.go-intech.com> wrote in message
> news:e%23sTcx%239DHA.1504@.TK2MSFTNGP12.phx.gbl...
> anything.
NO
return
and
>

No comments:

Post a Comment