> I would like to write a statement that gives me a list of all users in the
> "teaserusers" table and the last "logdate" entered into the table for each
> user listed.
Here's the way I'd think about that:
SELECT t.userid,
t.nickname,
t.firstname,
t.lastnames,
t.email,
t.registereddate,
lu.*
FROM teaserusers t, log_userstatus lu
WHERE t.usertid = lu.userid(+)
AND lu.logdate = (SELECT max(lu2.logdate)
FROM log_userstatus lu2
WHERE lu2.userid = t.userid)
Or:
WHERE t.usertid = lu.userid(+)
AND (lu.logdate = (SELECT max(lu2.logdate)
FROM log_userstatus lu2
WHERE lu2.userid = t.userid)
OR lu.logdate IS NULL )
Not sure...I'd have to play with this some to make sure the NULL joins are
handled right, but this is the basic idea if I understand your question. I
can't speak to the efficiency of the code since I just wrote it off the top
of my head....but I suspect its not very good. If log_userstatus is indexed
on logid and logid is sequentially assigned, you might buy some performance
by changing logdate to logid in the WHERE clause.
Brian
-----Original Message-----
From: Andres [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 02, 2001 4:54 PM
To: CF-Talk
Subject: RE: Help with a SQL statement
SELECT
teaserusers.userid,
teaserusers.nickname,
teaserusers.firstname,
teaserusers.lastnames,
teaserusers.email,
teaserusers.registereddate,
log_userstatus.*
FROM teaserusers LEFT JOIN log_userstatus
ON teaserusers.userid = log_userstatus.userid
This statement does two incorrect things:
1. doubles the rows of users according to how-many times they have entries
in the "log_userstatus" table
2. provides a NULL value to the userid of users who ARE NOT listed in the
"log_userstatus" table.
Am i way off?
thanx!
andres
-----Original Message-----
From: Greg Wolfinger [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 02, 2001 4:54 PM
To: CF-Talk
Subject: Re: Help with a SQL statement
Can we see your current SQL statement?
--Greg
----- Original Message -----
From: "Andres" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, January 02, 2001 4:42 PM
Subject: Help with a SQL statement
> I need some help with a sql statement:
> I have two tables:
>
> 1 ---------------> *
> teaserusers log_userstatus
> ----------- --------------
> userid (varchar) logid (varchar)
> nickname (text) userid (varchar)
> firstname (text) fieldchanged (varchar)
> email (text) logdate (datetime)
> etc... etc...
>
> The "teaserusers" table has the data about all users.
> The "log_userstatus" table keeps track of specific events and tracks their
> occurrence.
>
> These tables, although not explicitly related in the RDBMS, have a
> "one-to-many" relationship based on the "userid."
>
> I would like to write a statement that gives me a list of all users in the
> "teaserusers" table and the last "logdate" entered into the table for each
> user listed.
>
> Here's the catch, and i think this is what is preventing my code from
> working: Not all users have entries in the "log_userstatus" table. This
> means that a simple inner join will not work. I've also tried the left
outer
> and full outer joins, not providing the results i need.
>
>
> Is this possible? if so, can you all please help me write the sql code for
> it?
>
>
> Thanks for all your help!
>
> andres
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
