CODEWITHSUNDEEP
phpsqlcount
user2203703
user2203703

Reputation: 2005

only select/count verified and not banned users

Here is the code

SELECT username,count(username) FROM users WHERE status = '1' // $vuser to get the verified user only (it's the main query)

SELECT username FROM banned_users WHERE username = $vuser  // if yes $bad_user is true else false

Does it possible to only select/count verified users who doesn't exist in the banned_users with one sql query ?

Any idea please ?

Upvotes: 0

Views: 144

Answers (2)

juergen d
juergen d

Reputation: 204884

SELECT u.username, count(u.username) 
FROM users u
left outer join banned_users b on b.username = u.username
WHERE u.status = '1'
AND b.username is null
GROUP BY u.username

Upvotes: 5

JordiVilaplana
JordiVilaplana

Reputation: 485

Try with nested query:

SELECT username, COUNT(username) // Selects the `username` and `COUNT(username)` columns
FROM users // From the `users` table
WHERE status = '1' // Where its `status` is '1'
AND username NOT IN (SELECT b.username FROM banned_users b) // And its `username` is not at `username` column in the `banned_users` table

Upvotes: 1

Related Questions