Hi John,
>where r.name in (select distinct name from resources)
Is this working? If there are 30 records in all , the select distinct
clause may return 20
but when execute the where r.name in.. , the statement still return 30
records, because the other 10 name is also meet the criteria : where
r.name in... .
I had this problem before when I got a flat data file from a client and
try to make a set of relational tables. If anyone has a good sulotion
and willing to share with me, I would very much appreciate.
Thanks.
Sima
-----Original Message-----
From: John [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 24, 2001 11:21 PM
To: CF-Talk
Subject: RE: OT DBMS select distinct
The key word distinct does not work that way. It will only allow
duplicate
record sets but does nothing for individual fields unless it is a one
field
query. A work around is as follows but depending on the data set .
Hope
this helps.
John
select
r.ResourceID,
r.Name,
r.Description,
p.ApplicationID
from
Resources as r LEFT OUTER JOIN
Policies as p
ON
r.ResourceID = p.ResourceID
where r.name in (select distinct name from resources)
-----Original Message-----
From: Bryan LaPlante [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 24, 2001 10:04 PM
To: CF-Talk
Subject: OT DBMS select distinct
Sorry for the off topic but how do you select distinct only on one of
several items in a select statement.
I only want the unique names from the query below but this doesn't seem
to
be working.
Winnt 4 sp6
CF 4.5.2
Access or SQL 6.5
select
r.ResourceID,
distinct r.Name,
r.Description,
p.ApplicationID
from
Resources as r LEFT OUTER JOIN
Policies as p
ON
r.ResourceID = p.ResourceID
The error message
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Cannot include Memo, OLE, or
Hyperlink Object when you select unique values (r.Description).
Thanks in advance for your help
Bryan LaPlante
816-347-8220
[EMAIL PROTECTED]
http://www.netwebapps.com
Web Development
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
