Sorry, excuse my complete newbie DB skills but is that a SELECT DISTINCT on the query written below?
On 7/12/04 12:53 am, "Bryan Love" <[EMAIL PROTECTED]> wrote: > Just do a "SELECT DISTINCT..." > > -----Original Message----- > From: Stuart Kidd [mailto:[EMAIL PROTECTED] > Sent: Monday, December 06, 2004 4:16 PM > To: CF-Talk > Subject: Re: Joins > > Hi, I'm getting an error with both your reply and Michaels so I don't > know > if it's something I explained incorrectly. > > I'm getting this error: > > http://www.020.com/webs/020/content.cfm?writers > > > [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name > 'articleAuthenticated'. > > I do however have that column name. It is in the tbl_020articleDetails > table. > >>> <!-- Get author information --> >>> <cfquery name="GetAuthors" datasource="user020"> >>> SELECT tbl_020authorDetails.authorID, tbl_020articleDetails.authorID, >>> authorFirstName, authorSurname, articleAuthenticated >>> FROM tbl_020authorDetails, tbl_020articleDetails >>> WHERE tbl_020authorDetails.authorID = tbl_020articleDetails.authorID > and >>> tbl_020articleDetails.articleAuthenticated = 1 >>> </cfquery> > > Thanks, > > Saturday > > On 6/12/04 11:04 pm, "Adrocknaphobia" <[EMAIL PROTECTED]> wrote: > > >> Well there are a million ways to SQL a cat. Here is one: >> >> SELECT Q.authorID, A.authorFirstName, A.authorSurname, > A.articleAuthenticated >> FROM >> ( >> SELECT authorID FROM tbl_020articleDetails >> WHERE articleAuthenticated = 1 >> GROUP BY authorID >> ) Q INNER JOIN tbl_020authorDetails A ON Q.authorID = A.authorID >> >> Should do the trick. >> >> -Adam >> >> On Mon, 6 Dec 2004 22:45:37 -0000, Stuart Kidd <[EMAIL PROTECTED]> > wrote: >>> Hi guys, >>> >>> I've got a query whereby I am getting a list of all authors (table: >>> tbl_020authorDetails) in my database. I am cross referencing against >>> all the articles (tbl_020articleDetails) and only displaying the > authors >>> who have an article published (articleAuthenticated = 1). >>> >>> The problem I am coming across is that when an author has two > articles >>> published it is displaying their name twice in my list and of course > I >>> only want it outputted the once. >>> >>> Can anyone see where I'm going wrong? >>> >>> <!-- Get author information --> >>> <cfquery name="GetAuthors" datasource="user020"> >>> SELECT tbl_020authorDetails.authorID, tbl_020articleDetails.authorID, >>> authorFirstName, authorSurname, articleAuthenticated >>> FROM tbl_020authorDetails, tbl_020articleDetails >>> WHERE tbl_020authorDetails.authorID = tbl_020articleDetails.authorID > and >>> tbl_020articleDetails.articleAuthenticated = 1 >>> </cfquery> >>> >>> Thanks very much for your help. >>> >>> Saturday >>> >>> >> >> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Silver Sponsor - CFDynamics http://www.cfdynamics.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186406 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
