Thanks Jeff, Chris and Todd.
I had hoped to do this purely in SQL, but I think some post-query
processing is required. The main difficulty now is the sheer amount of
data involved.
-David
On Sun, 19 Nov 2000 16:09:06 -0500 Jeffry Houser <[EMAIL PROTECTED]>
writes:
>
> 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
