Linked Table

[StylusDesigns]

I am building a site to sell cd's and cassettes. There are currently over 600
albums from over 250 artists broke in to 20 categories. The thing that throws
me is that an artist can have albums in more then one category, so its not
straight forward one to many relationships. I need to be able to show all
artists that have albums in a particular category. I pass the cat_id that I
want, but then an artist doesn't have the a cat_id associated to it because it
could be one of many categories they fall into. I have the cat_id associated
to a album because an album can only fall in to one category. here is a
picture of my table relationships in MS Access
[url]http://www.ecsmaine.com/klaritymusic/relationships.jpg[/url] Can I search all albums
that have x cat_id then print it out. listed by
artist, then loop all albums that the artist
has only in that cat? I want it to look like Category: Rock. Artist 1
album 1 album 2 album 3 Artist 2 album 1
album 2 Artist 3 album 1 album 2 Also Later I will want
to click artist name, list all avalable albums, grouped by category by this
artist.

[TA-Selene]

Sure. To get all the atists/albums for a category: SELECT A.Artist_Name,
AL.Album FROM Artist A, Album AL WHERE A.ID = AL.Artist_ID AND AL.Cat_ID =
#Cat_ID# <cfoutput query='xxx' group='Artist_Name'> <b>#Artist_Name#</b><br />
<cfoutput>#Album#<br /></cfoutput><br /> </cfoutput> To get all the albums for
an artist by category, just switch the fields and output around.

[StylusDesigns]

Thanks.