Results 1 to 3 of 3

Thread: UNION confusION

  1. #1
    Join Date
    Dec 2002
    Location
    Austin, TX
    Posts
    2

    Question Unanswered: UNION confusION

    Hey, All.

    I've got a seemingly simple query that has turned into a bunch more head-throbbing than I ever expected!

    Basically, I have a Client table and a Logins table, and I'm trying to get a list of all users/Clients and their last LoginDate in a single query. My table structures are as follows:

    Client
    ------
    ClientID
    FirstName
    LastName

    Logins
    ------
    LoginID
    ClientID
    IP
    LoginDate

    My MDB (w/ the two tables and my current query) can be found at:
    http://www.eslacker.com/logins.mdb

    Basically, I'm either getting duplicate records (for each login) or only those users who have actually logged in (depending on how I tweak the query). What I'm going for is a list of ALL users/Clients and either their most recent LoginDate, or "", "never", etc.

    Any help is mucho appreciado! =)

    Ciao,

    -Jei.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    select ClientID
         , FirstName
         , LastName
         , IIF(ISNULL(LoginDate),'Never',LoginDate)
                 as LastLogin
      from Client
    left outer
      join Logins X
        on Clients.ClientID = X.ClientID
     where LoginDate is null
        or LoginDate
         = ( select max(LoginDate)
               from Logins
              where ClientID = X.ClientID )
    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Dec 2002
    Location
    Austin, TX
    Posts
    2

    Thumbs up

    Thank you Rudy!!!!!!!!

    I tweaked it a bit, but here's a slightly modified version that worked like a charm! Thanks for your suggestion!

    SELECT Client.ClientID
    , FirstName
    , LastName
    , Client
    , IP
    , IIf(ISNULL([LoginDate]),'Never',[LoginDate]) AS LastLogin

    FROM Client LEFT JOIN Logins AS X ON X.ClientID = [Client].[ClientID]
    WHERE (LoginDate IS NULL OR LoginDate = (SELECT MAX(LoginDate) FROM Logins WHERE Logins.ClientID = Client.ClientID))
    ORDER BY LoginDate DESC , Client;

    P.S. if anyone else is interested in the table structure, etc., an updated version of the MDB is at http://www.eslacker.com/logins2.mdb

    Ciao,

    -Jei.

Posting Permissions

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