Could be this join: INNER JOIN tblMusicStyles ON tblMusicStyles_X.musicstyleID = tblMusicStyles_X.musicstyleID
It doesn't reference tblMusicStyles at all. On 5/16/07, Will Tomlinson <[EMAIL PROTECTED]> wrote: > I have a tblMusicProducts table that links to a tblSongTitles many table. > There are three other linking tables that tie a particular product to more > than one style, voicing, or product type. > > I've been asked to show a list of song titles in alphabetical order. The > problem is, the song titles are listed multiple times due to all these > linking tables. I've beat my head against the wall trying to get the data > grouped correctly. > > I have a mess here... any ideas? > > Thanks, > Will > > SELECT tblMusicProducts.musicprodID, tblMusicProducts.musicprodname, > tblMusicProducts.musicprodprice, tblMusicProducts.musicprodimagelarge, > tblMusicProducts.musicproddescription, tblMusicProducts.musicprodlink, > tblMusicProducts.musicprodvendornum, tblSongTitles.musicprodID, > tblSongTitles.songtitleID, tblSongTitles.songtitlename, > tblVoicings.voicingname, tblVoicings.voicingID, tblVoicings_X.voicingID, > tblMusicProdTypes.musicprodtypeID, tblMusicStyles.musicstyleID > FROM tblSongTitles > INNER JOIN tblMusicProducts ON tblMusicProducts.musicprodID = > tblSongTitles.musicprodID > INNER JOIN tblVoicings_X ON tblMusicProducts.musicprodID = > tblVoicings_X.musicprodID > INNER JOIN tblVoicings ON tblVoicings_X.voicingID = tblVoicings.voicingID > INNER JOIN tblMusicStyles_X ON tblMusicProducts.musicprodID = > tblMusicStyles_X.musicprodID > INNER JOIN tblMusicStyles ON tblMusicStyles_X.musicstyleID = > tblMusicStyles_X.musicstyleID > INNER JOIN tblMusicProdTypes_X ON tblMusicProducts.musicprodID = > tblMusicProdTypes_X.musicprodID > INNER JOIN tblMusicProdTypes ON tblMusicProdTypes_X.musicprodtypeID = > tblMusicProdTypes.musicprodtypeID > GROUP BY tblMusicProdTypes.musicprodtypeID, tblMusicStyles.musicstyleID, > tblMusicProducts.musicprodID, > tblMusicProducts.musicprodname, tblMusicProducts.musicprodprice, > tblMusicProducts.musicprodimagelarge,tblMusicProducts.musicproddescription, > tblMusicProducts.musicprodlink, tblMusicProducts.musicprodvendornum, > tblSongTitles.musicprodID, tblSongTitles.songtitleID, > tblSongTitles.songtitlename , tblVoicings.voicingname, tblVoicings.voicingID, > tblVoicings_X.voicingID > ORDER BY tblSongTitles.songtitlename, tblVoicings.voicingID, > tblMusicProdTypes.musicprodtypeID, tblMusicProducts.musicprodprice, > tblMusicProducts.musicprodimagelarge, tblMusicProducts.musicproddescription, > tblMusicProducts.musicprodlink, tblMusicProducts.musicprodvendornum, > tblSongTitles.musicprodID, tblSongTitles.songtitleID, > tblVoicings.voicingname, tblVoicings_X.voicingID > > The output: > > <cfoutput query="getAllSongs" group="musicstyleID"> > <li><a href="detail.cfm?SID=#songtitleID#" title="#songtitlename# - > #DollarFormat(musicprodprice)#">#songtitlename#</a></li> > </cfoutput> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:278362 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
