kay,
try this:
<cfquery datasource="#dsn#" name="due1">
SELECT w.pkWebUser, w.Username, w.firstname,
w.lastname, w.dtPaid_until, w.isDisabled, w.dtCreated, w.fkLevel,
w.numFailed, w.fkCountries, w.ccNum, w.ccExpm, w.ccExpy,
w.ccType,
w.ccName, e.Email, l.USD, MAX(a.dtUpdated) as lastdateupdated
FROM WebUser w
INNER JOIN Email e ON w.fkEmail =e.pkEmail
INNER JOIN [Level] l ON w.fkLevel =l.pkLevel
LEFT OUTER JOIN WebUserArchive a ON w.pkWebUser = a.fkWebUser
WHERE w.ccNum IS NOT NULL
AND w.ccExpy IS NOT NULL
AND w.ccExpm IS NOT NULL
AND w.ccType IS NOT NULL
AND w.dtPaid_until <= #CreateODBCDate(DateAdd('d',7,Now()))#
AND w.dtDeleted IS NULL
AND w.numFailed <> 0
GROUP BY w.pkWebUser, w.Username, w.firstname,
w.lastname, w.dtPaid_until, w.isDisabled, w.dtCreated, w.fkLevel,
w.numFailed, w.fkCountries, w.ccNum, w.ccExpm, w.ccExpy,
w.ccType,
w.ccName, e.Email, l.USD
</cfquery>
~ dina
----- Original Message -----
From: "Kay Smoljak" <[EMAIL PROTECTED]>
To: "SQL" <[EMAIL PROTECTED]>
Sent: Thursday, May 02, 2002 2:14 AM
Subject: SQL distinct problem
> Hi all,
>
> More progress on that SQL Server query I got some help on the
other day.
> If there are multiple records in the table WebUserArchive, it
pulls
> multiple copies of the same record out of table WebUser. I've
been
> trying to stuff a DISTINCT in there somewhere but it's not
working. I
> have also tried doing a CF QoQ but they don't seem to support
complex
> enough syntax. Does anyone have a clue?
>
> Don't panic, thsoe are credit card numbers, but they are 1024
bit RSA
> asymmetrically encrypted :)
>
> <cfquery datasource="#dsn#" name="due1">
> SELECT DISTINCT w.pkWebUser, w.Username, w.firstname,
> w.lastname, w.dtPaid_until,
> w.isDisabled, w.dtCreated, w.fkLevel,
> w.numFailed, w.fkCountries,
> w.ccNum, w.ccExpm, w.ccExpy, w.ccType,
> w.ccName, e.Email,
> l.USD, a.dtUpdated
> FROM dbo.WebUser w
> INNER JOIN dbo.Email e ON w.fkEmail =
> e.pkEmail
> INNER JOIN dbo.[Level] l ON w.fkLevel =
> l.pkLevel
> LEFT OUTER JOIN dbo.WebUserArchive a ON
> w.pkWebUser = a.fkWebUser
> WHERE w.ccNum IS NOT NULL
> AND w.ccExpy IS NOT NULL
> AND w.ccExpm IS NOT NULL
> AND w.ccType IS NOT NULL
> AND w.dtPaid_until <=
> #CreateODBCDate(DateAdd('d',7,Now()))#
> AND w.dtDeleted IS NULL
> AND w.numFailed <> 0
> </cfquery>
>
> Thanks again,
> Kay.
>
>
_________________________________________________________________
_____
> Structure your ColdFusion code with Fusebox. Get the official
book at http://www.fusionauthority.com/bkinfo.cfm
> Archives: http://www.mail-archive.com/[email protected]/
> Unsubscribe:
http://www.houseoffusion.com/index.cfm?sidebar=lists
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
