Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: getting the right reults

    Hi,
    This is a really complicated issue and is hard to explain but i have the following:

    select name, MAX(table2.time) from table1 INNER JOIN table2 on table1.id = table2.id GROUP BY name

    which is fine and brings up the correct results but if I want to find out from those records what another field is in table 2 for each record it pulls up too many results (i want just the one result from table 2 and then find what user it is)

    if I do..

    select name, table2.username MAX(table2.time) from table1 INNER JOIN table2 on table1.id = table2.id GROUP BY name, table2.username

    .. it pulls up too many results cos there are different usernames

    if i dont group by table2.username then it give an error

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Coach me a bit here... If there can be multiple usernames, which one do you want? For a quick and dirty, you could try:
    PHP Code:
    SELECT
       name
    ,  Max(table2.username)
    ,  
    Max(table2.time)
       
    FROM table1
       INNER JOIN table2
          ON 
    (tabled1.id table2.id)
       
    GROUP BY name 
    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    6
    ok say i have these tables

    table 1
    ID Computer Name
    1 ws1
    2 ws2
    3 ws3
    4 ws4

    table 2
    ID User Time
    2 Bill 22/2/04 15:30
    2 Bob 22/2/04 16:40
    2 Bill 22/2/04 19:50
    2 Jack 22/2/04 13:21
    2 Jack 22/2/04 11:22
    2 Jack 22/2/04 13:10
    3 Jack 22/2/04 19:21
    ....
    Inner join table 2 on table1.id = table2.id

    i want to get all the records from table 1 and for each of those records i want to get the maximum Time from table 2. That i can do but I cannot extract the user name for each record because if i include the user colum in the select statement it will pul up a value for each user who was on the one machine.

    Sorry i cannot explain it well

    I just want to fin out who was the user from the maximum time for each computer

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That helps me understand. The solution depends on which SQL engine you are using. I'd try using:
    PHP Code:
    SELECT
       name
    ,  table2.username
    ,  table2.time
       FROM table1
       INNER JOIN table2
          ON 
    (table2.id table1.id)
       
    WHERE  table2.time = (SELECT Max(z.time)
          
    FROM table2 AS z
          WHERE  z
    .id table1.id
    -PatP

  5. #5
    Join Date
    Apr 2004
    Posts
    6
    Yeah that sort of works if i want to search on a users id for the last computer they were on but what if i want to find all the computers where he was the last one logged in?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Maybe I don't understand your question. Can't you just add the user name into the outer WHERE clause to see all of the computers (and times) where that particular user was the last one logged on?

    -PatP

Posting Permissions

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