Page 1 of 1
SQL Query
Posted: Mon Jul 16, 2007 4:27 am
by mad_phpq
feyd | Please use Code: Select all
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.
If you need more info let me know.
feyd | Please use[/syntax]Code: Select all
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]
Posted: Mon Jul 16, 2007 6:00 am
by volka
You're using mssql?
Then you're probably interested in
http://msdn2.microsoft.com/en-us/library/ms189463.aspx
Reverse the order and limit the result to one record.
Posted: Mon Jul 16, 2007 6:26 am
by mad_phpq
feyd | Please use Code: Select all
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
only returns one record.
feyd | Please use[/syntax]Code: Select all
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]
Posted: Mon Jul 16, 2007 7:07 am
by volka
Ah, the groupwise maximum.
I still prefer a LEFT JOIN that selects only those records that have no "greater" records on the right side
Code: Select all
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.
But you might want to look up sub querries
e.g. at
http://msdn2.microsoft.com/en-us/librar ... l.80).aspx
Posted: Mon Jul 16, 2007 7:12 am
by mad_phpq
Sorry to be a pain, and i really appreciate your help, but how can I incorporate that solution into my query.
Posted: Mon Jul 16, 2007 7:23 am
by volka
With a second JOIN.
The result of a [INNER,LEFT,RIGHT] JOIN is again (more or less) a record and you can combine two records via JOIN.
Code: Select all
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

Posted: Mon Jul 16, 2007 7:28 am
by mad_phpq
ok. excuse my ignorance. I get the following error when i run that query
Server: Msg 174, Level 15, State 1, Line 15
The isnull function requires 2 arguments.
Posted: Mon Jul 16, 2007 8:22 am
by volka
Posted: Mon Jul 16, 2007 8:47 am
by mad_phpq
i supposed i thought they were was a quick fix, or someone could do it for me.

Thanks anyway.
Posted: Mon Jul 16, 2007 8:56 am
by volka
replacing Isnull(b.logtime) by what is described at
http://msdn2.microsoft.com/en-us/library/ms188795.aspx is a quick fix.
Posted: Mon Jul 16, 2007 9:06 am
by mad_phpq
what is described there? Sorry, i'm getting really confused now. Cant you be more specific.
Posted: Mon Jul 16, 2007 9:09 am
by mad_phpq
ok. I did this, but its not returning all the records.
Code: Select all
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
Posted: Mon Jul 16, 2007 9:16 am
by volka
oh yeah, there's something wrong with ON u.id=a.id
Should users.id match transaction_log.id ?
Posted: Mon Jul 16, 2007 9:30 am
by mad_phpq
found it.
it should have read u.id=a.user_id
Code: Select all
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