SQL Statements

D

DS

Is there any difference between these 2 SQL statements?
The where clause...

1
SELECT PayName.PaymentNameID, PayName.PaymentName,
PayName.ExpirationDate, SecurityDetails.SecurityID,
SecurityDetails.Active, Privleges.PrivID, Privleges.PayTag
FROM (SecurityDetails INNER JOIN Privleges ON SecurityDetails.PrivID =
Privleges.PrivID) INNER JOIN PayName ON Privleges.PayTag =
PayName.PaymentType
WHERE (((PayName.PaymentName) Not Like "Coupon") AND
((PayName.ExpirationDate) Is Null) AND ((SecurityDetails.Active)=-1)) OR
(((PayName.ExpirationDate)>Now()))
ORDER BY PayName.PaymentName;

2
SELECT PayName.PaymentNameID, PayName.PaymentName,
PayName.ExpirationDate, SecurityDetails.SecurityID,
SecurityDetails.Active, Privleges.PrivID, Privleges.PayTag
FROM (SecurityDetails INNER JOIN Privleges ON SecurityDetails.PrivID =
Privleges.PrivID) INNER JOIN PayName ON Privleges.PayTag =
PayName.PaymentType
WHERE (((PayName.PaymentName) Not Like "Coupon") AND
((PayName.ExpirationDate) Is Null) AND ((SecurityDetails.Active)=-1)) OR
(((PayName.PaymentName) Not Like "Coupon") AND
((PayName.ExpirationDate)>Now()) AND ((SecurityDetails.Active)=-1))
ORDER BY PayName.PaymentName;

Will they work the same?

Thanks
DS
 
J

John Nurick

Paste the two WHERE statements into Notepad and reformat them,
eliminating superfluous parentheses and using indents to make it clearer
what belongs with what. You'll end up with something like this:

1
WHERE (
(PayName.PaymentName Not Like "Coupon")
AND
(PayName.ExpirationDate Is Null)
AND (SecurityDetails.Active=-1)
) OR (
PayName.ExpirationDate)>Now()
)

2
WHERE (
(PayName.PaymentName Not Like "Coupon")
AND
(PayName.ExpirationDate Is Null)
AND (SecurityDetails.Active=-1)
) OR (
(PayName.PaymentName Not Like "Coupon")
AND
(PayName.ExpirationDate>Now())
AND
(SecurityDetails.Active=-1)
)

which makes the difference clear: 1 includes ALL records where
ExpirationDate is in the future, while 2 restricts them by PaymentName
and Active as well.
 
D

DS

John said:
Paste the two WHERE statements into Notepad and reformat them,
eliminating superfluous parentheses and using indents to make it clearer
what belongs with what. You'll end up with something like this:

1
WHERE (
(PayName.PaymentName Not Like "Coupon")
AND
(PayName.ExpirationDate Is Null)
AND (SecurityDetails.Active=-1)
) OR (
PayName.ExpirationDate)>Now()
)

2
WHERE (
(PayName.PaymentName Not Like "Coupon")
AND
(PayName.ExpirationDate Is Null)
AND (SecurityDetails.Active=-1)
) OR (
(PayName.PaymentName Not Like "Coupon")
AND
(PayName.ExpirationDate>Now())
AND
(SecurityDetails.Active=-1)
)

which makes the difference clear: 1 includes ALL records where
ExpirationDate is in the future, while 2 restricts them by PaymentName
and Active as well.
Click to expand...
Thanks, that clears a lot of questions up for me! I appreciate the input!
Thanks
DS
 
Top