CODEWITHSUNDEEP
mysqlsql
BryanLavinParmenter
BryanLavinParmenter

Reputation: 416

Remove results based on other table

I currently have a query displaying the top users in my game by most money.

$q = mysqli_query($link, "SELECT `money`, `name`, `id` FROM `users` WHERE `money` > 0 ORDER BY `money` DESC LIMIT 10");
$r = 1;
while($row = mysqli_fetch_assoc($q)) {
    //Code here.
    ++$r;
}

Where $r is the rank of the user (from 1-10).

What I'm trying to do is filter out users who are currently banned on the game. There is no column in the users table for whether or not a user is banned, but there is a separate bans table that looks like this:

bans
`id` INT(11) PRIMARY AUTO-INCREMENT
`user` INT(11)
`reason` TEXT
`length` INT(11)

The user value in the bans table is the exact same as the id value in the users table.

I'm trying to filter out all users from the first query if they exist in the bans table.

Upvotes: 2

Views: 37

Answers (2)

jpw
jpw

Reputation: 44881

You can use a not exists query for example:

SELECT `money`, `name`, `id` 
FROM `users` u 
WHERE `money` > 0 
  AND NOT EXISTS (SELECT 1 FROM Bans b WHERE b.user = u.id)
ORDER BY `money` DESC 
LIMIT 10

This would remove the banned users from the result which is what I think you want - if it's the other way around just remove the not.

Upvotes: 1

Jens
Jens

Reputation: 69440

You have to use left join for that:

SELECT `money`, `name`, ´users`.`id` FROM `users`  left join `bans` on user.id = bans.user WHERE bans.is is null and`money` > 0 ORDER BY `money` DESC LIMIT 10");

Upvotes: 1

Related Questions