Help with Joining/Displaying Tables in Query

[dahveed76]

Hi all...

 

I'm using PHP/MySQL on a list of theaters, both past and present, that show the Rocky Horror Picture Show for www.RockyHorror.com . Let me give you a little background on how this film's showings work:

 

A theater (in this case, i call it "Venue" and this has its own table) shows RHPS at regular intervals (could be weekly, bi-weekly, every third Saturday of the month, etc.)

 

I made what I call a "venue instance" to go with it, which has details on a showing for each particular venue (venue name, first date/year of showings, last date/year of showings, frequency, time, venue phone, address, website, lat & lon, day of the week, whether it's a regular showing [once a month or more] or a special showing [less than once a month, with its own specific date].)

 

Then I made a "cast" table, with info on the cast (cast name, websites, start date/year at venue, end date/year at venue, etc.)

 

The last thing I made was a "cast instance" table, which links the "Cast" table and "Venue Instance" table together.

 

So now I have 4 tablesets:

 

showings_venue

showings_venueinstance

showings_cast

showings_castinstance

 

1) I'm pretty sure there's a more efficient way of joining the data on the page than what i've done here:

 

SELECT * FROM showings_venue

LEFT JOIN showings_venueinstance

ON vinst_venueid = venue_id

LEFT JOIN showings_castinstance

ON cinst_venueinstanceid = vinst_id

LEFT JOIN showings_cast

ON cast_id = cinst_castid

 

2) The tricky part is this:

 

Some venues have more than one cast instance (for example, one venue has separate casts for their Friday and Saturday shows).

Some venues have more than one venue instance (i.e. they stopped shoing RHPS and then started again a few years later).

Some casts perform at more than one venue at the same time, or move from venue to venue.

 

What I want to do is be able to display:

 

Cast Info page: detailed info on the cast, as well as any venues they perform at, in the past or present, in a looping table

 

Venue Info page: detailed info on the venue, as well as any casts that perform there, in the past or present, in a looping table

 

I have a good portion of the site working, but this part is where I've hit a wall. I'm fairly certain, though, that the way I've done Part 1 is hindering me from doing Part 2.

 

Here's the page to get a notion of what i'm talking about:

http://www.rockyhorror.com/participation/showingslist_listings.php?type=r

 

-David

[fenway]

I'm not sure what you think the problem is... how you issue your queries depends on what you start with.  If you start with a casd_id, you have to go all the way back to get the vues, etc.