My one disclaimer is that this untested psuedo-code, but should
give you the idea. You didn't say how you need to output the info.
Tab deliminated? Comma? Are you exporting all Genres as one field
or as multiple fields?
But, something like this should do the trick:
<CFQUERY name="GetMovies">
select *
from movies
</CFQUERY>
<CFLOOP query="GetMovies">
<CFQUERY name="GetGenres">
select genres.*
from genres, moviesGenres
Where moviesGenres.movieID = #getmovies.movieID# and
genres.genreID = moviesGenres.genreID
</CFQUERY>
<CFSET GenreList = ""
<CFLOOP query="getGenres">
<CFIF GenreList = "">
<CFSET GenreList = #getGenres.genre#>
<CFELSE>
<CFSET GenreList = GenreList & "," & getGenres.genre>
</CFIF>
</CFLOOP>
<CFSET masteroutput = #getmovies.movie# & "," #genrelist#>
<CFFILE action="append" file="filename.txt" output=#Masteroutput#>
</CFLOOP>
Make sense? If you want to group the genres together, just use
a different deliminator than what you're using to separate it
from the move title.
My mind is going blank at this point, but Queries are stored
internally as arrays, or are they stored as structures? If it's
an array, you can use the arraytolist function on the getGenres
query, and get rid of the loop inside the loop completely.
David Shadovitz wrote:
>
> A client wants me to extract data from his relational database into a
> text file. The database contains information about movies, and he wants
> one line per movie. I do not know how to flatten the data. For example,
> say these are the tables and fields:
>
> Movies: MovieID, MovieName
> Genres: GenreID, GenreName
> MoviesGenres: MovieID, GenreID
>
> And suppose the movie "Wild West" falls into genres Drama, Romance, and
> Western.
>
> The client wants the single line for this movie to look like:
> Wild West, Drama, Romance, Western
>
> Any suggestions?
>
> Thanks.
> -David
> ________________________________________________________________
> GET INTERNET ACCESS FROM JUNO!
> Juno offers FREE or PREMIUM Internet access for less!
> Join Juno today! For your FREE software, visit:
> http://dl.www.juno.com/get/tagj.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Structure your ColdFusion code with Fusebox. Get the official book at
>http://www.fusionauthority.com/bkinfo.cfm
>
> Archives: http://www.mail-archive.com/[email protected]/
> Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
--
Jeff Houser
mailto:[EMAIL PROTECTED]
AIM: Reboog711 | ICQ: 5246969 | Phone: 860-229-2781
--
Author of Instant Cold Fusion 4.5
ISBN: 0-07-213238-8
Due out 3rd Quarter 2001
--
DotComIt, LLC
Computer Consultant specializing in database driven web data
ColdFusion, Lotus Notes/Domino
--
Half of the Alternative Folk Acoustic Duo called Far Cry Fly
http://www.farcryfly.com
http://www.mp3.com/FarCryFly
--
Does Everyone Think I'm a Cynical?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
