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