SQL Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

SQL Query

Post by mad_phpq »

feyd | Please use

Code: Select all

,

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

,

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]
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

Post by mad_phpq »

feyd | Please use

Code: Select all

,

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

,

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]
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

Post by mad_phpq »

Sorry to be a pain, and i really appreciate your help, but how can I incorporate that solution into my query.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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 ;)
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Ok. you need http://msdn2.microsoft.com/en-us/library/ms188795.aspx for transact-sql (mssql)
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

Post by mad_phpq »

i supposed i thought they were was a quick fix, or someone could do it for me. :lol: Thanks anyway.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

replacing Isnull(b.logtime) by what is described at http://msdn2.microsoft.com/en-us/library/ms188795.aspx is a quick fix.
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

Post by mad_phpq »

what is described there? Sorry, i'm getting really confused now. Cant you be more specific.
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

oh yeah, there's something wrong with ON u.id=a.id
Should users.id match transaction_log.id ?
mad_phpq
Forum Commoner
Posts: 85
Joined: Fri Apr 27, 2007 5:53 am

Post 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
Post Reply