I have three tables, worship_songs, worship_sets, worship_song_lists.
worship_songs:
worship_songID
worship_song_title
worship_song_lyrics
worship_sets:
worship_setID
worship_set_title
worship_set_date
worship_song_lists:
worship_song_listID
worship_songID_fk
worship_setID_fk
I have a spry dataset that displays the current songs associated with a set
list, as well as a dataset from which users can see a list of songs from which
to composes a set list. Neither of these is difficult; however, I would like
to be able to display an icon next to each song in the master list that
indicates whether or not the song is in the currently selected set list.
I am currently attempting the following:
<cfquery name="rsWorshipSongs" datasource="#application.dsn#">
SELECT *
FROM worship_songs
JOIN worship_song_lists oN
worship_songs.worship_songID = worship_song_lists.worship_songID_fk
GROUP BY worship_songID
</cfquery>
<cfsavecontent variable="rsWorshipSongs">
<?xml version=""1.0"" encoding=""UTF-8""?>
<cfoutput>
<songs>
<cfloop from="1" to="#rsWorshipSongs.recordCount#" index="i">
<song>
<id>#rsWorshipSongs.worship_songID[i]#</id>
<title>#rsWorshipSongs.worship_song_title[i]#</title>
<cfif rsWorshipSongs.worship_setID_fk[i] EQ arguments.setID>
<saved>yes</saved>
<cfelse>
<saved>no</saved>
</cfif>
</song>
</cfloop>
</songs>
</cfoutput>
</cfsavecontent>
The returned data in the "saved" attributes does not accurately reflect the
songs which belong to the individual sets. If I take off the grouping, the
records that do exist in the worship_song_lists are duplicated, while those
that do not are not.
I am seriously pulling my hair out on trying to get this query to return all
the songs that exist while also showing their status in relation to the
selected set list.
Any help would be greatly appreciated!
Thanks!
Joel
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade to ColdFusion 8 and integrate with Adobe Flex
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:293562
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
