Yes, yes it does. For whatever caveman reasons (no offense to cavemen) I skipped right over that when looking at all of the different types of joins. I was sure you hadn't overlooked that kind of behavior. You thought of everything else.
Anyway... 10 seconds later, with the assistance of your brain, all is well. On Sep 30, 10:15 pm, Mark Mandel <[email protected]> wrote: > Does this > help:http://docs.transfer-orm.com/wiki/Transfer_Query_Language.cfm#Selecti... > > Mark > > On Thu, Oct 1, 2009 at 3:10 PM, whostheJBoss > <[email protected]>wrote: > > > > > > > > > I am having a problem with setDistinctMode() in Transfer and am trying > > to figure out the correct way to using distinct with joins. > > > I am running a query with TQL: > > > <cfset var qList = "" /> > > <cfset var tQuery = "" /> > > > <cfsavecontent variable="qList"> > > <cfoutput> > > > FROM tags.tag > > join bookmarks.bookmark as bookmark > > join bookmarklinks.bookmarklink as bookmarklink ON > > bookmark.bookmarkLink > > join links.link as link ON bookmarklink.link > > WHERE link.userID = :userID > > > </cfoutput> > > </cfsavecontent> > > > <cfset tQuery = variables.transfer.createQuery(qList) /> > > <cfset tQuery.setDistinctMode(true) /> > > <cfset tQuery.setParam("userID",arguments.userID) /> > > <cfreturn variables.transfer.listByQuery(tQuery) /> > > > This creates SQL of: > > > select distinct tags.tagID, tags.tagTitle, bookmark.title, > > bookmark.sortOrder, bookmark.status, bookmark.bookmarkID, > > bookmarklink.linkNote, bookmarklink.linkTitle, bookmarklink.sortOrder, > > bookmarklink.bookmarkLinkID, link.linkURL, link.linkTitle, > > link.userID, link.linkID FROM tags inner join taglinks on > > taglinks.tagID = tags.tagID inner join bookmarks bookmark ON > > taglinks.bookmarkID = bookmark.bookmarkID inner join bookmarklinks > > bookmarklink ON bookmark.bookmarkLinkID = bookmarklink.bookmarkLinkID > > inner join links link ON bookmarklink.linkID = link.linkID WHERE > > link.userID = '1' > > > Since I have the setDistinctMode(true) parameter, I was expecting to > > get distinct tags. Instead, my query contains multiple references to > > the same tagID > > > results: > > > id title > > 1 sports > > 3 funny > > 1 sports > > 1 sports > > 2 news > > > Sports should only be in the query once. > > > When I change the SQL to: > > > select distinct tags.tagID, tags.tagTitle FROM tags inner join > > taglinks on taglinks.tagID = tags.tagID inner join bookmarks bookmark > > ON taglinks.bookmarkID = bookmark.bookmarkID inner join bookmarklinks > > bookmarklink ON bookmark.bookmarkLinkID = bookmarklink.bookmarkLinkID > > inner join links link ON bookmarklink.linkID = link.linkID WHERE > > link.userID = '1' > > > I run the query manually, without Transfer and then the results are: > > > id title > > 1 sports > > 3 funny > > 2 news > > > So, it works when I remove all the extra stuff that came before the > > FROM. > > > Basically I'm asking... how can I do a distinctMode select while using > > joins? When I use joins the extra: > > > bookmark.title, bookmark.sortOrder, bookmark.status, > > bookmark.bookmarkID, bookmarklink.linkNote, bookmarklink.linkTitle, > > bookmarklink.sortOrder, bookmarklink.bookmarkLinkID, link.linkURL, > > link.linkTitle, link.userID, link.linkID > > > is added before the FROM, which causes the distinct not to work. > > Simply removing these lines and using a regular SQL query works, but > > surely there is a way to do this in Transfer? > > > All I want before FROM is: > > > select distinct tags.tagID, tags.tagTitle FROM > > > The other stuff causes the distinct not to work, but the joins are > > required for getting the proper data. I just can't figure out how to > > have Transfer let me still use the join without adding all of the > > extra columns before FROM. > > > Thanks! > > -- > E: [email protected] > T:http://www.twitter.com/neurotic > W:www.compoundtheory.com --~--~---------~--~----~------------~-------~--~----~ Before posting questions to the group please read: http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer You received this message because you are subscribed to the Google Groups "transfer-dev" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/transfer-dev?hl=en -~----------~----~----~----~------~----~------~--~---
