and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
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.
[syntax="sql"]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
Also, can someone tell me how i can pull the results from the query analyzer in MS SQL to something like Excel.
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Top will return the top record, but not top for each user.
[syntax="sql"]
SELECT TOP 1 users.id,users.username,users.email,users.firstname,users.surna me, 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
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
SELECT
a.user_id, 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
WHERE
Isnull(b.logtime)
The "trick" is the AND a.logtime<b.logtime . If there is no record with a greater value in logtime than the current record all fields of the right side table are set to NULL in the joined result record. And only those we allow in the result set with the WHERE Isnull(b.logtime) clause.
SELECT
a.user_id, u.username, 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.id
WHERE
Isnull(b.logtime)
If you feel uncomfortable with the JOINs you really should look up sub queries. Many people find them more ...comprehensible
SELECT
a.user_id, u.username, 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.id
WHERE
b.logtime IS NULL
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
brilliant. Now, one last thing. What can i do to find all the users that are not in the transaction log