need help with SELECT logic

trefrog · #1 (**permalink**) 02-13-06, 14:36

I have 2 tables and I need to select conditionally from both, I don't know why this isn't working. The purpose of this is (obviously?) to enforce unique usernames while preserving data about 'dead' users.
Here is my little test database:

Code:

mysql> select userID, username from user_info;
+----------+----------+
| userID   | username |
+----------+----------+
| 00000001 | nwarden  |
| 00000002 | ahajas   |
| 00000004 | nwarden  |
+----------+----------+

mysql> select userID, SubStatus from user_stats;
+----------+------------+
| userID   | SubStatus  |
+----------+------------+
| 00000001 | Active     |
| 00000002 | Terminated |
| 00000004 | Terminated |
+----------+------------+

This is the query that I'm using:

Code:

mysql> SELECT i.userID, i.username FROM user_info AS i, user_stats AS s WHERE i.
username='nwarden' AND s.SubStatus<>'Terminated';
+----------+----------+
| userID   | username |
+----------+----------+
| 00000001 | nwarden  |
| 00000004 | nwarden  |
+----------+----------+

But this is not what I expected. This is what I want:

Code:

+----------+----------+
| userID   | username |
+----------+----------+
| 00000001 | nwarden  |
+----------+----------+

What am I doing wrong?

RandyRiegel · #2 (**permalink**) 02-13-06, 15:25

I get the desired results by using a join like this:

Code:

select user_info.userid, username from user_info
    join user_stats on user_info.userid = user_stats.userid 
    where username = 'nwarden' and 
        user_stats.substatus <> 'Terminated';

trefrog · #3 (**permalink**) 02-13-06, 17:19

Voila! Merci Beaucoup!

Internet Services

Web Development

Digital Marketing

Consumer Tech