In fact, looking at it again you don't need to join on to the tbl_020authorDetails table. The AuthorID being in the joining table should be enough. Unless you want a column or two from tbl_020authorDetails.
Ade -----Original Message----- From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED] Sent: 25 September 2005 16:34 To: CF-Talk Subject: Re: Damn Queries! Wow, yours is a lot more simple than my complicated convoluted way! I'll definitely give your one the thumbs up! Thanks Ade, Saturday On 25 Sep 2005, at 16:29, Adrian Lynch wrote: > This should work: > > SELECT f.FeatureName > FROM tbl_020articleFeatures f > INNER JOIN YourJoiningTable j ON ( f.FeatureID = j.FeatureID ) > INNER JOIN tbl_020authorDetails a ON ( j.AuthorID = a.AuthorID ) > WHERE a.AuthorID = #CLIENT.authorID# > > Ade > > -----Original Message----- > From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED] > Sent: 25 September 2005 16:07 > To: CF-Talk > Subject: Damn Queries! > > > Hi, > > I have a table named tbl_020articleFeatures which has fields > featureID and featureName. > > I also have a table named tbl_020authorDetails which has authorID as > a field (as well as other stuff). > > I made a 'cross table' which is called tbl_020authorFeatures - this > table will have rows to establish which authors are authorised to > write which particular features. In here the fields I have are > authorFeatureID, featureID and authorID. > > I am trying to write a query which will pull only the features which > e particular author can write. > > At the moment I have it in 2 queries but i wouldn't be surprised if i > could just have one: > > <!--- find all feature that this author is authorised to > write about ---> > <cfquery name="GetAuthorFeatures" datasource="user020"> > SELECT authorFeaturesID, featureID, authorID > FROM tbl_020authorFeatures > WHERE authorID = #client.authorID# > </cfquery> > > <!--- find all feature types ---> > <cfquery name="GetArticleFeatureTypes" datasource="user020"> > SELECT featureID, featureName > FROM tbl_020articleFeatures > WHERE featureID = '#GetAuthorFeatures.featureID#' > ORDER by featureName ASC > </cfquery> > > And at the moment this is only pulling the first feature ID. > > If anyone could be of any help i would be grateful. > > Thanks, > > Saturday > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219191 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
