Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    7

    Question Unanswered: need help with SELECT logic

    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?

  2. #2
    Join Date
    Sep 2003
    Posts
    22
    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';

  3. #3
    Join Date
    Jan 2006
    Posts
    7
    Voila! Merci Beaucoup!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •