0

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 ?

user2203703
  • 1,955
  • 4
  • 22
  • 36

2 Answers2

5
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
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • I made a little change to my Q,could you check it again please ? thank you. – user2203703 May 20 '13 at 10:49
  • @user2203703: I updated my answer and added a `group by` clause. – juergen d May 20 '13 at 10:52
  • does it possible to use any other join case ? what about inner join ? – user2203703 May 20 '13 at 11:06
  • You have to use a `left join`. See [here](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html) for a good explanation. – juergen d May 20 '13 at 11:08
  • I know i'm asking to much..but i swear i tried left join before i open the Q and din't work with me and i thought it not possible to do that..could you please add + the left join case to your answer please ? thank you. – user2203703 May 20 '13 at 11:14
  • I used the left join in my answer. `left join` is the same as `left outer join` – juergen d May 20 '13 at 11:15
  • They are the same. See [here](http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server) – juergen d May 20 '13 at 11:19
1

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
JordiVilaplana
  • 485
  • 3
  • 9