CODEWITHSUNDEEP
mysqlsql
Artemkller545
Artemkller545

Reputation: 999

MySQL select username only if username doesn't exist in other table

I need to fetch usernames for my system, but if they exist in the table punishement_banned, it won't fetch that username.

I have made a query which should do that work, but for some reason, the query returns 0 values.

I have 1 row in punishement_banned with 1 name, total 2 names in the players table, so it must display 1 row.

SELECT players.username FROM players LEFT JOIN punishement_banned a ON a.username = players.username WHERE players.username != a.username;

But if I run this query, it will show the 2 names:

SELECT players.username FROM players LEFT JOIN punishement_banned a ON a.username = players.username;

What is wrong with my query?

Upvotes: 2

Views: 2815

Answers (5)

Raging Bull
Raging Bull

Reputation: 18767

Two alternatives:

  1. Using IN operator:

    SELECT username 
FROM players 
WHERE players.username NOT IN (SELECT username FROM punishement_banned)

  2. Using LEFT JOIN:

    This would be faster than 1st method if you have thousands of records in the table.

    SELECT players.username FROM players 
LEFT JOIN punishement_banned a ON a.username = players.username
WHERE a.username=NULL

Upvotes: 0

jkirchne
jkirchne

Reputation: 121

I have created sqlfiddle: http://www.sqlfiddle.com/#!2/fd650/4/0

SELECT username 
FROM players 
WHERE NOT EXISTS  
(  
   SELECT username 
   FROM punishement_banned 
   WHERE players.username = punishement_banned.username 
)

Upvotes: 2

Mattias Åslund
Mattias Åslund

Reputation: 3907

Your statement will find any username that can be matched against a row that doesnt have that username, which effectively is any username.

Use this:

SELECT players.username FROM players WHERE players.username NOT IN( SELECT username FROM punishement_banned )

Upvotes: 0

echo_Me
echo_Me

Reputation: 37243

you have two contradicted conditions here

   ON a.username = players.username WHERE players.username != a.usernam

you need to use NOT IN

  SELECT username FROM players 
  WHERE players.username NOT IN (select username from punishement_banned )

Upvotes: 0

Christian Cederquist
Christian Cederquist

Reputation: 513

This should do the job:

SELECT username 
FROM players 
WHERE players.username NOT IN 
(SELECT username FROM punishement_banned)

The problem is, you join the tables on the usernames being equal, and then exclude the usernames equal to each other.

Upvotes: 1

Related Questions