Results 1 to 9 of 9
  1. #1

    PHP and MySQL -- joins

    I was wondering if it was possible to do multiple joins with the same table on different columns as i can't seem to figure it out.

    I have a table where i store concert setlists. Thus is has information on venue, date, location, etc.... It also has columns for the setlist of the concert.

    So there are columns titled song1, song2, song3 that store a forgein key to another table where information about the song is stored. Information includes intro, song name, outro, etc...

    now i can do a left join on 1 column for the songs but i can't seem to do it for all the columns to get the all the info.

    my code is:

    PHP Code:
    SELECT FROM $TableName1 left join $TableName2 ON $TableName1.song1 $TableName2.count 
    I really want to do something like:
    PHP Code:
    SELECT FROM $TableName1
     left join $TableName2 ON $TableName1
    .song1 $TableName2.count 
     left join $TableName2 ON $TableName1
    .song2 $TableName2.count 
     left join $TableName2 ON $TableName1
    .song3 $TableName2.count 
     left join $TableName2 ON $TableName1
    .song4 $TableName2.count 

    etc
    ... 
    The problem I see is that the column names that i join into the main table are not unique after the first join. I'm not sure if there is anyway to fix this or not. My database design could be totally off too for all i know.

    So maybe somebody can help? Thanks!

  2. #2
    based on the theory of relational databases you must create new table with next structure and delete fields song1, song2, .... from concert table:

    TABLE SETLIST (new)

    ID
    ConcertID
    SongID

    And then operate through this table
    HostNodeList Web Host Directory, DEV.INTOEX.COM - products for online business

    Experienced web-developer | PHP | Smarty | Zend | Databases | Graphic design - looking for long-time relationship

  3. #3
    I'm still learning how to do relational databases so i'm a bit confused about this "next structure" you are talking about.

    If i create a new table (setlist) like you suggest how do I link up a list of songID's to make a setlist. I would still have the problem of having many columns that store an ID for a song wouldn't I?


    Thanks.

  4. #4
    Join Date
    Jan 2003
    Posts
    1,715
    I usually call it a 'bridge' table, but I'm not sure what the standard term is.

    So you have...
    concert_info (ConID, date, time, etc)
    concert_sets (ConID, SetID) [concert & set bridge]
    set_info (SetID, artist, etc)
    set_songs (SetID, SongID) [set & song bridge]
    song_info (SongID, title, etc)

    The bridges allow for a 'many to many' relationship where the same sets or songs may be in several concerts and sets. This 'tall' format takes some getting used to if you grew up on spreadsheets, but it offers a lot more flexibility. Indexing becomes a really good next thing to learn, though.

    In case you do run into needing the same table twice (for questions like 'what set plays song1 and song2?'), you want to use aliases for the table
    LEFT JOIN table1 AS alias1 ... LEFT JOIN table1 AS alias2
    Game Servers are the next hot market!
    Slim margins, heavy support, fickle customers, and moronic suppliers!
    Start your own today!

  5. #5
    thanks hiryuu, i think it makes more sence now that you laid it all out. So just to make sure, in the set_songs table i would have the same SetID listed multiple times with a different SongID associated with it for the songs in a particular set. right? If so then i think i understand the design.

    Tho one thing i don't see is how does this preserve the order of songs played in the set. Would it just be the implied order that is found in the set_songs table. Thus i have to be aware of how i input into the database?

    Would it be bad form to have a third thing in the set_songs table which would hold a setPlacementID. setPlacement would be something like set opener, set closer, encore, etc... I noticed both of your "bridge" tables only have 2 IDs and wasn't sure if this was just proper form to have it this way or just coincidence.

    Thanks

  6. #6
    Join Date
    Jan 2003
    Posts
    1,715
    thanks hiryuu, i think it makes more sence now that you laid it all out. So just to make sure, in the set_songs table i would have the same SetID listed multiple times with a different SongID associated with it for the songs in a particular set. right? If so then i think i understand the design.
    Or the same SongID listed multiple times with different SetIDs, depending on your perspective. Yes. The SetID + SongID combo must be unique, but each component may appear multiple times.

    Would it be bad form to have a third thing in the set_songs table which would hold a setPlacementID. setPlacement would be something like set opener, set closer, encore, etc... I noticed both of your "bridge" tables only have 2 IDs and wasn't sure if this was just proper form to have it this way or just coincidence.
    Mostly coincidence. You can have other fields in there (set order is a good example), but they must relate to the SetID + SongID key, and not to one component or the other (such as title).
    Game Servers are the next hot market!
    Slim margins, heavy support, fickle customers, and moronic suppliers!
    Start your own today!

  7. #7
    Join Date
    Mar 2004
    Location
    New Zealand
    Posts
    533

    Re: PHP and MySQL -- joins

    Well, your table design is bad as someone else has explained, but for your given table design you would want to use table aliases.
    PHP Code:
    SELECT FROM $TableName1
     left join $TableName2 ON $TableName1
    .song1 $TableName2.count 
     left join $TableName2 
    AS b ON $TableName1.song2 b.count 
     left join $TableName2 
    AS c ON $TableName1.song3 c.count 
     left join $TableName2 
    AS d ON $TableName1.song4 d.count 

  8. #8
    cool. Thanks for all the help everybody. I'm just trying to learn database design and at the same time developed a useful project for myself.

    I've been revamping my database design and wanted to see if you guys see any problems with it or have suggestions on doing it a better way...

    I've basically extended on what hiryuu initally wrote...

    some comments:

    venue name is how it is becuase i wanted to keep track of changes in venue names over the years. So if somebody remembers seeing a show a X but now it's called Verizon Wireless or something he can still search for venue X and find the show.



    I wasn't sure how to handel guests on songs at a specific show. There could be many guests on a song also. I was temped to include guestID in set_songs_order (SetID, SongID, OrderID) but decided on making it it's own table because the Order a song is in doesn't really have anything to do with a guest or not.



    I was also thinking about including source info with the taperID as a taper has a source for all recordings. Some may be as detailed as X taped with microphone A and whateverf others could as simple as it's a soundboard taped by X.

    So i could have a taper entered once with many different source info or have a taper entered many times with different source for each. I choosed to do the former as that seemed like the best way but maybe somebody will have a better way to do this.




    concert_info (ConID, date, city, state, etc)
    concert_venue (ConID, VenueID) [concert & venue bridge]
    venue_info (VenueID, name)

    set_info (SetID, artist)
    concert_sets (ConID, SetID) [concert & set bridge]

    set_songs_order (SetID, SongID, OrderID) [set & song & order bridge]
    song_info (SongID, intro, song, interpolation, outro, segue)
    order_info (OrderID, placement)

    set_song_guests (SetID, SongID, guestID) [set & song & guest bridge]
    guests_info(guestsID, name)

    con_taper (ConID, TaperID, SourceID) [concert & taper & source bridge]
    taper_info (TaperID, name)
    source_info (SourceID, dat/md/dsbd/cass/etc... , microphone, etc...)


    I think this covers everything i want in the database but i might come up with something else in the future....


    Thanks again all

  9. #9
    Join Date
    Jan 2003
    Posts
    1,715
    City & State should be part of the venue info, unless you expect the venue to move, as well as change names
    Game Servers are the next hot market!
    Slim margins, heavy support, fickle customers, and moronic suppliers!
    Start your own today!

Posting Permissions

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