Page 1 of 1

Multiple Joins - one query

Posted: Mon Nov 04, 2002 9:01 am
by Goo
Ok, I am dealing with 7 tables:

Play (play_id, scheduled, actual, event_type, event_id)
Song (song_id, title, length, ending, cat, composer, publisher, add_date)
Artist (artist_id, name, keyword1, keyword2, keyword3, keyword4)
Album (album_id, title)
Song_Artist_Roster (song_id, artist_id)
Song_Album_Roster (song_id, album_id)
Event (event_id, content, length)

Play is bacically the playlist of a radio station, this includes songs, commercial breaks, dj time, etc.

I need to print out a playlist for the entire day that includes the scheduled air time for everything, song name, artist name, album name, etc.

Events that are not songs are labled with an event_type of anything but 'M'. All of these other event are held in the Event table and will be referenced as such.

So far, I have the playlist printing out all of the songs, but none of the non-song events (commercial breaks, station identification, dj talk time, etc).

Here is my query so far:
SELECT
play.play_id AS play_id,
play.scheduled AS scheduled,
song.title AS song_title,
song.length AS song_length,
artist.name AS artist_name,
album.title AS album_title
FROM
play, song, artist, album,
song_artist_roster AS artist_rost,
song_album_roster AS album_rost
WHERE
play.event_type = 'M' AND
play.event_id = song.song_id AND
song.song_id = artist_rost.song_id AND
artist.artist_id = artist_rost.artist_id AND
song.song_id = album_rost.song_id AND
album.album_id = album_rost.album_id
ORDER BY scheduled ASC";


I need to figure out how to add in the logic so that it reads from the Event table rather than the Song/Artist/Album tables if it encounters an event_type of anything but 'M'.

The only solution so far that I can come up with is to run two separate querys, dump the two results into an array, and sort based on the Scheduled variable (datetime). But it seems like there should be an easier way.

Any help would be really appreciated, let me know if you need more explaination!

Thanks

Posted: Thu Nov 14, 2002 3:34 pm
by MeOnTheW3
Try this:

Code: Select all

"SELECT
A.play_id,
A.scheduled,
B.title as song_title,
B.length as song_length,
C.name as artist_name,
D.title as album_title,
G.event_id as eventID,
G.content,
G.length as event_length
FROM
play A,
song B,
artist C,
album D,
song_artist_roster E,
song_album_roster F,
event G
WHERE
((A.event_type='M' AND 
A.event_id=B.song_id AND 
B.song_id=E.song_id AND 
C.artist_id=E.artist_id AND 
B.song_id=F.song_id AND 
D.album_id=F.album_id) OR
A.event_id=G.event_id)
ORDER BY scheduled ASC";

Posted: Sat Nov 16, 2002 12:32 am
by MeOnTheW3
did that help?????