Little SQL question

[nmajdan]

Ok, I'm trying to make a decent database for setlists for live shows (I would LOVE to make something like this but thats a little beyond my db capabilities). I've got my db set up (I hope) but this project is gonna require MANY complex queries. And I'm already stuck. I'll list the tables in question:

∙Venue(venue_id, name1, name2, name3, city, state, capacity, type)
∙Show(show_id, date, venue_id, tour_id, noSetlist?)
∙Song(song_id, s_name, album_id, isCover, original, isNotStudio)
∙ShowSongs(showSong_id, show_id, song_id, songNumber, isSegue, isCloser, isEncore1, isEncore2, isSetBreak, songDesc, songLength)
∙Personnel(person_id, p_name, p_instrument1, p_instrument2)
∙ShowPersonnel(personnelSong_id, show_id, song_id, personnel_id)
NOTE: there are 4 additional tables: Album, Tour, Guest, & ShowGuests

What I'm aiming for is a list that will contain one row for every song performed. In my database, I've entered 90 songs over 4 shows so the resulted table should have 90 rows. This SQL statement gets me everything I want except for the Personnel/ShowPersonnel info:

select Date, name1 as [Venue], City, State, songnumber as [Location in Set], s_name as [Song], isSegue as [Segues], isSetBreak as [Set Break], isCloser as [Closer], isEncore1 as [Encore 1], isEncore2 as [Encore 2], songDesc as [Description]
from showsongs, show, song, venue
where showsongs.show_id=show.show_id
and showsongs.song_id=song.song_id
and show.venue_id=venue.venue_id;

FYI, personnel is any guest who plays during the show. now, I basically want two extra columns appended to this list. Ever cell in these two new columns should be blank except for the the song in which the person guested. So far, everything I have tried has resulted in only displaying the one record where the person guested. Like this one:

select Date, name1 as [Venue], City, State, songnumber as [Location in Set], s_name as [Song], isSegue as [Segues], isSetBreak as [Set Break], isCloser as [Closer], isEncore1 as [Encore 1], isEncore2 as [Encore 2], songDesc as [Description], p_name as [Personnel], p_instrument1 as [Instrument]
from showsongs, show, song, venue, showPersonnel, Personnel
where showsongs.show_id=show.show_id
and showsongs.song_id=song.song_id
and show.venue_id=venue.venue_id
and showpersonnel.personnel_id=personnel.person_id
and showpersonnel.song_id=showsongs.song_id;

Where am i going wrong?

 

[SydneyGeek]

How have you joined the tables? a quick look at the field list suggests that you have the data reasonably well normalised but there are no joins in the query statements. Typically, the syntax goes something like
SELECT [all the fields here]
FROM [Table1 INNER JOIN Table1.Keyfield=Table2.Keyfield...]
WHERE [Filters and criteria here]
ORDER BY [Sort order, if any];

Try using the wizards or Design view in Access to build up some queries and see how they go. Then you can go into SQL view and check the effect of changing your settings.

Denis

 

[nmajdan]

How have you joined the tables? a quick look at the field list suggests that you have the data reasonably well normalised but there are no joins in the query statements. Typically, the syntax goes something like
SELECT [all the fields here]
FROM [Table1 INNER JOIN Table1.Keyfield=Table2.Keyfield...]
WHERE [Filters and criteria here]
ORDER BY [Sort order, if any];

Try using the wizards or Design view in Access to build up some queries and see how they go. Then you can go into SQL view and check the effect of changing your settings.

Denis

they looked joined to me. my joins are in the where clause. i've set the pks of one table to equal the fks of another. thats how i was taught. i've never been taught the other joins. the wizards are not working for me. i've tried everything.

 

[ChrisUK]

Hi.

I'm finding it difficult to visualise what it is you're trying to retrieve.

If you're after all the showsongs between two dates you will need to:

1. Have the dates in the where clause

2. Have ALL the joins in the primary table

I'm not sure what you're trying to do will work, i.e. joint table1 -> table2 - table3 -> table4

You need to join table2, table3 and table4 to table1

But I could have completly missunderstood!

Chris

 

[nmajdan]

Here's a chunk of what the first SQL statement gives me when I run it. This is what I want. It should contain one entry for every song:

Date Venue City State Location in Set Song Segues Set Break Closer Encore 1 Encore 2 Description
3/8/2004 House of Blues West Hollywood CA 25 Mother Funker Yes No No Yes No
3/8/2004 House of Blues West Hollywood CA 26 The Devil Went Down To Georgia No No No Yes No
3/9/2004 House of Blues Anaheim CA 1 The One No No No No No
3/9/2004 House of Blues Anaheim CA 2 Crash Burn No No No No No
3/9/2004 House of Blues Anaheim CA 3 You Lost Me There Yes No No No No
3/9/2004 House of Blues Anaheim CA 4 Mulling It Over No No No No No
3/9/2004 House of Blues Anaheim CA 5 Sweet & Broken No No No No No
3/9/2004 House of Blues Anaheim CA 6 Freedom No No No No No
3/9/2004 House of Blues Anaheim CA 7 Stand No No No No No
3/9/2004 House of Blues Anaheim CA 8 The Joker No No No No No
3/9/2004 House of Blues Anaheim CA 9 This Ache No No No No No
3/9/2004 House of Blues Anaheim CA 10 But Anyway No Yes No No No
3/9/2004 House of Blues Anaheim CA 11 100 Years No Yes No No No
3/9/2004 House of Blues Anaheim CA 12 Rage No No No No No
3/9/2004 House of Blues Anaheim CA 13 Save His Soul No Yes No No No
3/9/2004 House of Blues Anaheim CA 14 Stumble and Fall No No No No No
3/9/2004 House of Blues Anaheim CA 15 Defense & Desire No No No No No
3/9/2004 House of Blues Anaheim CA 16 Business As Usual Yes No No No No
3/9/2004 House of Blues Anaheim CA 17 Partner In Crime Yes No No No No
3/9/2004 House of Blues Anaheim CA 18 Business As Usual No No No No No
3/9/2004 House of Blues Anaheim CA 19 Hook No No No No No
3/9/2004 House of Blues Anaheim CA 20 Belive Me No No No Yes No
3/9/2004 House of Blues Anaheim CA 21 Love & Greed No No No Yes No
3/12/2004 Val Air Ballroom West Des Moines IA 1 The One Yes No No No No

Now on some songs, a person will sit in and play during the song. What I want is basically the above result except with 2 extra columns that state the person guested on this song with this instrument. my 2nd query is way off. So for instance, on 3/9, Beth Hart sang during The Joker. So in the 2 columns to the right of that entry, it should list her name and her instrument (vocals). everything other cell in those columns should be blank.


Thanks for the help. I have a zipped up copy of my db located here.

 

[SydneyGeek]

Here's what I was talking about re the syntax in Access (this IS an Access db, isn't it?). SQL is from the Sales By Category query in Northwind.

SELECT Categories.CategoryID, Categories.CategoryName, Products.ProductName, Sum([Order Details Extended].ExtendedPrice) AS ProductSales
FROM Categories INNER JOIN (Products INNER JOIN (Orders INNER JOIN [Order Details Extended] ON Orders.OrderID = [Order Details Extended].OrderID) ON Products.ProductID = [Order Details Extended].ProductID) ON Categories.CategoryID = Products.CategoryID
WHERE (((Orders.OrderDate) Between #1/1/1997# And #12/31/1997#))
GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName
ORDER BY Categories.CategoryName;


Denis