CODEWITHSUNDEEP
sqlsql-server
Chen Xu
Chen Xu

Reputation: 57

Result not filtered

Trying to count the number of canceled trips after filtering out the banned users (Client+Driver), however it seems like my where clause is not working

Trips Table Trips Table

Users Table

Users Table

SELECT COUNT(*)
FROM Trips
WHERE Status = 'cancelled_by_driver' OR Status = 'cancelled_by_client'
AND Client_Id NOT IN (SELECT Users_Id
                      FROM Users
                      WHERE Users.Banned = 'Yes')
AND Driver_Id NOT IN (SELECT Users_Id
                      FROM Users
                      WHERE Users.Banned = 'Yes')
GROUP BY Request_at

Upvotes: 0

Views: 46

Answers (2)

user1854438
user1854438

Reputation: 1842

select count(*)
from trips, users
where trips.Status in ('cancelled_by_driver','cancelled_by_client')
and (trips.Client_Id = user.id and user.banned != 'Yes')
and (trips.Driver_Id = user.id and user.banned != 'Yes');

Upvotes: 0

mgrollins
mgrollins

Reputation: 651

You need a further parentheses around the first OR term in your WHERE. The OR term is causing logical errors:

SELECT COUNT(*)
FROM Trips
WHERE (Status = 'cancelled_by_driver' OR Status = 'cancelled_by_client')
AND Client_Id NOT IN (SELECT Users_Id
                      FROM Users
                      WHERE Users.Banned = 'Yes')
AND Driver_Id NOT IN (SELECT Users_Id
                      FROM Users
                      WHERE Users.Banned = 'Yes')
GROUP BY Request_at

Upvotes: 2

Related Questions