RE: OT DBMS select distinct
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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: OT DBMS select distinct
Ok I see where you are going with this. your suggested work around does get the unique values but the output duplicates the same value the number of time it is referenced in the table. I will play around with it and worse case I will just use the group field in the cfoutput. Thanks for the heads up. Bryan - Original Message - From: John [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, May 24, 2001 10:20 PM 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
