[SOLVED] SQL Query Help

[mad_php]

Hi,

 

I'm trying to to extract information from two tables. A users table and a log table. The log table logs all user logins. I'm trying to get a list of all users and when they last logged in. I've came up with the following query so far and it returns all log in times. I just want the last login time.

 

 

SELECT users.id,users.username,users.email,users.firstname,users.surname, transaction_log.logtime
FROM users,transaction_log
WHERE users.id = transaction_log.user_id
        AND users.guest = 1
        AND transaction_log.log_category_id = 1
ORDER BY transaction_log.id

 

I've tried TOP 1, but it only returns one record instead of one per user.

 

 

Also, can someone tell me how i can pull the results from the query analyzer in MS SQL to something like Excel.

 

If you need more info let me know.

[mad_php]

Solved it

 

SELECT
  a.user_id,u.username,u.email,u.firstname,u.surname,u.active, a.logtime
FROM
  transaction_log AS a
LEFT JOIN
  transaction_log AS b
ON
  a.user_id=b.user_id
  AND a.logtime<b.logtime
JOIN
  users AS u
ON
  u.id=a.user_id
WHERE
  b.logtime IS NULL
  AND u.guest = 1
ORDER BY a.user_id