How Do I : 3 Table Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How Do I : 3 Table Query

Author

Topic

zerbert
Starting Member

4 Posts

Posted - 2003-05-20 : 08:51:50

I have a small music database with the following tables:

Bands
BandID, BandName

Cds
CdID, BandId, CdName

Songs
SongID, BandID, CdID, TrackNum, SongTitle

Currently, when i add a compilation cd to the database the Cds table is updated to have "Various" as the band name for the album, with "Various" and the band for each track on the cd added as a Band in the band table if required.

I need to write a query to get all songs by a band which will return the band name, cd name, track number and song title for each - my main problem being that the BandID stored with the CdName in the cd table relates to the band "Various".

It requires a query to get the CdID from the songs table and replace this with the appropriate CdName, and similarly replace the BandID with BandName.

This is way over my head, can anyone help me out here?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-20 : 09:06:58

This will show all songs, the band that performs the song, which CD it is on, and which Band (or "Various") "owns" the CD.

select S.songTitle, B1.BandName as Band, S.TrackNum, CDs.CDName, B2.BandName as CDBand
FROM
Songs S
INNER JOIN
CDs ON
S.CDID = CDs.CDID
INNER JOIN
Bands B1
ON
S.BandID = B1.bandID
INNER JOIN
Bands B2
ON
CDs.BandID = B2.BandID

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-20 : 09:07:50

quote:
Currently, when i add a compilation cd to the database the Cds table is updated to have "Various" as the band name for the album, with "Various" and the band for each track on the cd added as a Band in the band table if required.

I understand this to mean that Cds.CdName = 'Various' for a compilation CD, but Bands.Bandname is the actual band name...

Start with..

SELECT
Songtitle, TrackNum, BandName, CdName
FROM Songs S
INNER JOIN Cds C ON C.CDid = S.CdID AND C.BandID = S.BandID
INNER JOIN Bands B on B.BandID = S.BandID

The select above forms a table of all songs with the BandName and CDName

quote:
I need to write a query to get all songs by a band which will return the band name, cd name, track number and song title for each - my main problem being that the BandID stored with the CdName in the cd table relates to the band "Various".

You can modify the above query to replace the CDName 'Various' with the BandName for a particular band if you must??

SELECT
Songtitle, TrackNum, BandName,
CASE WHEN CDName = 'Various' THEN BandName ELSE CDName END AS CDName
FROM Songs S
INNER JOIN Cds C ON C.CDid = S.CdID AND C.BandID = S.BandID
INNER JOIN Bands B on B.BandID = S.BandID
WHERE BandName = 'Grand Funk Railroad'

I added the WHERE to limit the result to a particular band.

Is this close to what you wanted?

Sam

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-20 : 09:08:21

SELECT Distinct
Bandname, SongTitle
FROM
Songs
Inner join Cds on
Songs.cdid = cds.cdid
Inner join Bands on
Bands.Bandid = cds.bandid or bands.bandid = songs.bandid
where bandname = 'band'

?

-------
Moo.

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-20 : 09:15:25

quote:
SELECT Distinct
Bandname, SongTitle
FROM
Songs
Inner join Cds on
Songs.cdid = cds.cdid
Inner join Bands on
Bands.Bandid = cds.bandid or bands.bandid = songs.bandid
where bandname = 'band'

Shouldn't it be AND bands.bandid = songs.bandid

Sam

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-20 : 09:19:37

quote:

Shouldn't it be AND bands.bandid = songs.bandid

Sam

I'm not sure. All this "various" stuff confused me.

-------
Moo.

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-20 : 09:26:15

For ambiguous situations, I sometimes use MAYBE or PERHAPS instead of AND or OR.

Sam

zerbert
Starting Member

4 Posts

Posted - 2003-05-20 : 09:28:55

hehe, yeah it confuses me too!

As an example, here is the state of my database after i add a compilation cd called "Singles 1" with two tracks "Nirvana - teen spirit" and Pearl Jam - "Alive"

Bands
BandID, BandName
1 "Various"
2 "Nirvana"
3 "Pearl Jam"

Cds
CdID, BandId, CdName
1 1 "Singles 1"

Songs
SongID, BandID, CdID, TrackNum, SongTitle
1 2 1 1 "teen spirit"
2 3 1 1 "Alive"

If i did a search for Nirvana i would expect the query to return:

"Nirvana", "Singles 1", (Track) "1", "teen spirit"

Hope that clarifies things a little, i will try the above suggestions when i get home tonight, cheers for all input so far!

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-20 : 09:33:30

I think we helped with a homework problem

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-20 : 09:45:30

You want to join to the BANDS table twice -- two separate instances of the same table. There are two references to the bands table -- one by the CD (that's one join), and one by the song (that's the other).

One join returns the bandName from the CD. The other returns the bandname from the track.

Don't make it more confusing than it needs to be !

(see my first post on this thread for the query)

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-20 : 10:03:20

I saw your query Jeff, and vague as the requirements are, I like to believe that the BandID for a 'Compilation CD' will match the BandID for each song on the track. If it isn't a compilation CD, (different songs have different bandnames) then I'm not sure it makes sense for the CDs table to point to a single bandname in Bands.

All it would take for me to be wrong and you to be right is a word from the DB designer...

In either case, it seems it isn't a well thought out table design, if it doesn't account for differences in 'compilation' CDs and 'Various Artists' Cds.

Sam

zerbert
Starting Member

4 Posts

Posted - 2003-05-20 : 13:01:37

No, its not my homework honest :)

I just do a bit of DJing in my spare time and can never find any songs that i put on homemade compilation cds so i have a db of my songs on my laptop (which is at home), as well as on the club web pages to allow people to check for requests etc..

Cheers for all the input, i will try them out tonight.

zerbert
Starting Member

4 Posts

Posted - 2003-06-02 : 07:53:26

Just to say cheers again for all the input, Jeff, spot on first reply, was just what i was looking for! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources