you could always try select distinct *, but that would cause an error with text fields and other BIG fields... ----- Original Message ----- From: "Tom Smith" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, December 05, 2002 4:48 PM Subject: Re: [ cf-dev ] distinct
> you will get unique rows, but some of the data in each column maybe > duplicated.... for example... > > USERID, OTHERID > > 1,2 > 1,3 > 1,4 > 2,2 > 2,4 > > are all distinct rows to return if you are using something like: > > select distinct userid, otherid from table > > where as your query is using a * to return fields... so not sure which > distinct you are trying to use anymore. > > > > ----- Original Message ----- > From: "Russ 'Snake' Michaels" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, December 05, 2002 4:19 PM > Subject: RE: [ cf-dev ] distinct > > > > hmm, so how I only get back unique rows across the join. > > here is ma query. > > > > <cfquery name="get_reg" datasource="recruitment2" blockfactor="100"> > > SELECT * > > FROM users_address, users_cats UC, distinct_users_EB EB, > > distinct_users_fields UF > > WHERE UC.UID = users_address.UID > > AND EB.UID = users_address.UID > > AND UF.UID = users_address.UID > > <cfif form.field neq ""> > > AND UF.fieldID = #form.field# > > </cfif> > > <cfif form.jobcat neq ""> > > AND UC.catID = #form.jobcat# > > </cfif> > > <cfif form.ebasis neq ""> > > AND EB.EBID = #form.ebasis# > > </cfif> > > <cfif form.salary neq ""> > > AND users_address.sal = '#form.salary#' > > </cfif> > > ORDER BY NAME_TWO > > </cfquery> > > > > -----Original Message----- > > From: Tom Smith [mailto:[EMAIL PROTECTED]] > > Sent: 05 December 2002 16:14 > > To: [EMAIL PROTECTED] > > Subject: Re: [ cf-dev ] distinct > > > > > > it put the rows together at this point for the distinct... the results > are > > correct.... > > > > ----- Original Message ----- > > From: "Russ 'Snake' Michaels" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Thursday, December 05, 2002 3:48 PM > > Subject: [ cf-dev ] distinct > > > > > > > I am sure this is weird, but correct me if I am wrong. > > > > > > I have lookup tables like this > > > > > > USERID, OTHERID > > > > > > both columns will have multiple copies of values. > > > e.g > > > > > > 1,2 > > > 1,3 > > > 1,4 > > > 2,2 > > > 2,4 > > > > > > I need to get back only distinct UDERID's > > > > > > so a select distinct USERID from table > > > > > > works fine > > > but as I also need the other column, when I add select DISTINCR USERID, > > > OTHERID > > > I get multiple rows containing the same USERID back again > > > > > > what's up with that > > > > > > /Russ > > > > > > > > > > > > > > > -- > > > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > > For additional commands, e-mail: [EMAIL PROTECTED] > > > For human help, e-mail: [EMAIL PROTECTED] > > > > __________________________________________________ > > > > Do You Yahoo!? > > > > Everything you'll ever need on one web page > > > > from News and Sport to Email and Music Charts > > > > http://uk.my.yahoo.com > > > > > > -- > > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > For human help, e-mail: [EMAIL PROTECTED] > > > > > > -- > > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > For human help, e-mail: [EMAIL PROTECTED] > > __________________________________________________ > Do You Yahoo!? > Everything you'll ever need on one web page > from News and Sport to Email and Music Charts > http://uk.my.yahoo.com > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > For human help, e-mail: [EMAIL PROTECTED] __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
