This will give you the items that only exist in log_userstatus and
teaserusers but you will have to build on it to only get the newest date for
each one. You could loop through an array with another sql to bring out the
newest date.
SELECT teaserusers.userid,
teaserusers.nickname,
teaserusers.firstname,
teaserusers.lastnames,
teaserusers.email,
teaserusers.registereddate,
log_userstatus.userid,
log_userstatus.logdate
from teaserusers, log_userstatus
where exists (Select log_userstatus.userid
from log_userstatus
where log_userstatus.userid = teaserusers.userid)
and teaserusers.userid = log_userstatus.userid
cami
----- Original Message -----
From: "Andres" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, January 03, 2001 8:44 AM
Subject: RE: Help with a SQL statement
> 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
