SQL Question!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SQL Question!

  1. #1
    Join Date
    Oct 2003
    Posts
    7

    SQL Question!

    User table
    USERID......Name.......STATUS
    1.............Owen........active
    2.............Tom.........active
    3.............Jimmy.......active
    4.............Eason.......Inactive


    Log table
    ID........USERID......LastAccess
    1.........1.............10/21/2003
    2.........1.............10/22/2003
    3.........1.............10/23/2003
    4.........2.............09/21/2003
    5.........2.............09/25/2003
    6.........3.............10/21/2003
    7.........4.............10/21/2003

    How can I form a SQL to pull out all the ACTIVE user from user table, plus the user lastest lastaccess date.

    Report will looke like
    1.............Owen........active........10/23/2003
    2.............Tom.........active........09/25/2003
    3.............Jimmy.......active........10/21/2003

    SELECT u.userid, u.name, u.status, l.lastaccess
    FROM U.user, L.log
    Where u.status = 'active' and .....???????

    Thanks for advice!

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Code:
    SQL> select * from user1;
    
            ID NAME                 STATUS
    ---------- -------------------- ---------------
             1 Owen                 active
             2 Tom                  active
             3 Jimmy                active
             4 Eason                inactive
    
    SQL> select * from log1;
    
            ID     USERID L_ACCESS
    ---------- ---------- ---------
             1          1 21-OCT-03
             2          1 22-OCT-03
             3          1 23-OCT-03
             4          2 21-SEP-03
             5          2 25-SEP-03
             6          3 21-OCT-03
             7          4 21-OCT-03
    
    7 rows selected.
    
    SQL> select userid, name, status, max(l_access)
      2  from user1, log1
      3  where user1.id = userid
      4  and status = 'active'
      5  group by userid, name, status;
    
        USERID NAME                 STATUS          MAX(L_ACC
    ---------- -------------------- --------------- ---------
             1 Owen                 active          23-OCT-03
             2 Tom                  active          25-SEP-03
             3 Jimmy                active          21-OCT-03

  3. #3
    Join Date
    Oct 2003
    Posts
    7
    I haven't use the Group function before.

    Thanks!

    Best Regards!
    Owen

Posting Permissions

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


Click Here to Expand Forum to Full Width