CODEWITHSUNDEEP
mysql
Sasha Grievus
Sasha Grievus

Reputation: 2686

How to exclude records satisying two conditions at the same time?

I have a fairly complicate query

SELECT g.guidUser,u1.username,g.sent,g.accepted,g.declined,g.rewarded,g.guidUserVersus,u2.username AS usernameUserVersus
FROM games g 
INNER JOIN userdata u1 ON g.guidUser=u1.guidUser 
INNER JOIN userdata u2 ON g.guidUserVersus=u2.guidUser 
WHERE 
((g.sent!="" OR g.accepted!="" OR g.declined!="") 
AND g.rewarded="" AND (g.guidUser='1' OR g.guidUserVersus='1')) 
ORDER BY g.guidUser, g.idGame

Now I want to exclude records having at the same time

(g.accepted!="" AND g.guidUserVersus='1')

I tried

SELECT g.guidUser,u1.username,g.sent,g.accepted,g.declined,g.rewarded,g.guidUserVersus,u2.username AS usernameUserVersus,g.idGameVersus,g.timeFinal 
FROM games g 
INNER JOIN userdata u1 ON g.guidUser=u1.guidUser 
INNER JOIN userdata u2 ON g.guidUserVersus=u2.guidUser 
WHERE 
((g.sent!="" OR g.accepted!="" OR g.declined!="") 
AND g.rewarded="" AND (g.guidUser='1' OR g.guidUserVersus='1')) 
** AND !(g.accepted!="" AND g.guidUserVersus='1') **
ORDER BY g.guidUser, g.idGame

But it seems is not selecting records that it should, like having g.accepted!="" AND g.guidUserVersus='2'

Here it is the fiddle http://sqlfiddle.com/#!9/737e61/1

Upvotes: 0

Views: 27

Answers (1)

Gixuna
Gixuna

Reputation: 86

EDIT: You need to replace AND with OR inside the condition.

SELECT g.guidUser,u1.username,g.sent,g.accepted,g.declined,g.rewarded,g.guidUserVersus,u2.username AS usernameUserVersus,g.idGameVersus,g.timeFinal 
FROM games g 
INNER JOIN userdata u1 ON g.guidUser=u1.guidUser 
INNER JOIN userdata u2 ON g.guidUserVersus=u2.guidUser 
WHERE 
((g.sent!="" OR g.accepted!="" OR g.declined!="") 
AND g.rewarded="" AND (g.guidUser='1' OR g.guidUserVersus='1')) 
AND NOT (g.accepted!="" OR g.guidUserVersus='1')
ORDER BY g.guidUser, g.idGame

Upvotes: 2

Related Questions