Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    1

    Unanswered: Multiple joins from same tables

    Hi,

    I have the following database tables:

    tblVenue:
    venue_id | venue_name
    -----------------------
    1 | Theatre
    2 | Exhibition Centre
    3 | Hotel


    tblEvent:
    event_id | event_name | venue_id1 | venue_id2 | venue_id3
    ----------------------------------------------------------
    1 | My First Event | 1 | null | null
    2 | My Second Event | 2 | 3 | 1

    etc etc


    I want to return a recordset which shows the name of the event and the name(s) of the venues the event is at, like this:

    My First Event:
    Theatre

    My Second Event:
    Exhibition Centre
    Hotel
    Theatre


    How do I do this with a SQL query?

    I've got the following join, but only joins the first venue:

    Code:
    SELECT * FROM tblEvent INNER JOIN tblVenue ON tblEvent.venue_id1 = tblVenue.venue_id;
    I'm using MS SQL, not that it should make much difference.

    Thanks for any pointers folks!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your events table needs to be redesigned.

    Drop the venue_idX fields.
    Create a third table with the following columns: event_id, venue_id and declare them as a composite primary key
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gvee
    Create a third table with the following columns: event_id, venue_id and declare them as a composite primary key
    i'd go a bit further, and add a datetime column to the PK

    reason: since it is apparent that an event can take place in more than one venue, i thought it might be reasonable to allow for an event to take place in the same venue more than once

    kind of like how ac/dc added a second show to their toronto gig due to everwhelming ticket demand

    see Multiple joins on same field? - SitePoint Forums
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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