Try "SELECT DISTINCT", I beleive this will help with what you're looking for.
----- Original Message ----- From: "Will Tomlinson" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Wednesday, May 16, 2007 4:59 PM Subject: Trying to group this output... >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> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:278360 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
