Heather,
I will try this, and continue building upon it.
thanks for your help!
andres
-----Original Message-----
From: Heather Haindel [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 02, 2001 7:35 PM
To: [EMAIL PROTECTED]
Subject: RE: Help with a SQL statement
I am trying to figure this out... I've tried a whole
bunch of different things, but I just can't get
everything to work at once.
Right now, I've got two queries...
/*this gives us the most current logs*/
select userid, max(logdate)
from log_userstatus
group by userid
/*this give us the users*/
select userid, nickname
from teaserusers
Here's the closest I've got so far. It returns the
userid and latest log date for each user... I don't
know how to get other info for each record...
begin
declare @userid varchar(25)
declare cursorname SCROLL cursor for
select t.userid
from teaserusers t
open cursorname
fetch FIRST from cursorname into @userid
while (@@FETCH_STATUS <> -1)
begin
select userid, max(logdate)
from log_userstatus
where userid = @userid
group by userid
fetch next from cursorname into @userid
end
close cursorname
deallocate cursorname
end
--- Andres <[EMAIL PROTECTED]> wrote:
> 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
