Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    7

    Combine multiple rows into one

    I'm looking for a way to combine several records/rows into one in order to make indexing our products easier.

    Currently we sell electronic equipment along with CD's, but our CD's use several tables (artists, songs, etc) and I don't know the best way to search the product descriptions and the CD song titles faster.

    We have 2,000 products and half are CD's. The table that holds the CD tracks is just under 10,000, and our song catalog is also pushing 7,000.

    I'd like to combine all the songs from a single CD into one in order to make it easier to search the product descriptions and artist/songs.

    Hope I made this clear. If not post a reply and I'll try again.

    Randy

  2. #2
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    It sounds like you have three tables to join: 'song', 'track', and 'CD'. 'CD' might be called 'album'. 'Track' likely has a foreign key pointing to 'song', and another foreign key pointing to 'CD'. If that's the case, you might be able to create a view that joins the three tables and run your searches on that.

  3. #3
    Join Date
    Dec 2002
    Posts
    7
    Originally posted by bcrockett
    It sounds like you have three tables to join: 'song', 'track', and 'CD'. 'CD' might be called 'album'. 'Track' likely has a foreign key pointing to 'song', and another foreign key pointing to 'CD'. If that's the case, you might be able to create a view that joins the three tables and run your searches on that.

    That's exactly the way I'm currently doing it (which makes me feel better). Since the data never really changes once it's in, I'm trying to make a temporary "summary" table in the following format:
    ==========================================
    DISC_ID | DISC_TITLE | TRACKS
    ==========================================
    001 Disc 1 Song 1, song2, song3, etc
    | 002 Disc 2 Song 1, song2, song3, etc
    ==========================================

    I've toyed with the idea of storing TRACKS(song/artist/key) as XML, parsing them out at run time, and replacing with HTML as needed.

    I just wanted to get away from the overhead of running a query of all 10, 000 + tracks everytime we need to display the data.

    I'll play with the XML thing and see if that works.

    Wish me luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •