CODEWITHSUNDEEP
mysqljoin
Bill Blinn
Bill Blinn

Reputation: 13

Clues needed for joining 3 tables

This seems as if it should be easy but I haven't managed to create the proper query or to find an equivalent situation. Let's say I have 3 tables that I'd like to join: Artist, Album, and Track. (Actually I don't have these tables but the tables I'm working with have equivalent relationships.)

Each artist can have many albums and each album can have many tracks.

The artist table is indexed on artist_id and contains information about the artist (name, address, phone number, and such).

The album table is indexed on artist_id and album_id. It contains the name of the album and other information (recording date, location, etc).

The track table is indexed on artist_id, album_id, and track_nbr. It contains the name of the track, length, and such.

What I'm trying to do is create a report that would look like this:

Artist|Album|Track#|Track Name
The Wizard of Frobozz|Zork I|01|The White House
The Wizard of Frobozz|Zork I|02|Eaten by a Grue
The Wizard of Frobozz|Zork I|03|Take the Sword
The Wizard of Frobozz|Zork I|04|Don't Forget the Lantern
The Wizard of Frobozz|Zork II|01|Waiting for the Volcano Gnome
The Wizard of Frobozz|Zork II|02|On the Left Bank of Zork
The Wizard of Frobozz|Zork II|03|In the Oddly Angled Room

I can join the artist and album tables to get all of the albums but when I add the track table, I get only one track per album-artist combination.

This seems like it should work but doesn't:

select a.name, l.album, t.track_nbr, t.track_name
from track t, album l, artist a 
where t.artist_id = l.artist_id
and l.artist_id = a.artist_id
and t.album_id = a.album_id
order by a.name, l.album, t.track_nbr;

My guess is that I need to explicitly declare a join and possibly add parentheses to group the joins but I haven't been able to find documentation that I can fully comprehend to do this.

So ... Help? Thanks!

Upvotes: 1

Views: 60

Answers (3)

Dan Applegate
Dan Applegate

Reputation: 346

Shouldn't this:

and t.album_id = a.album_id

Actually be this?

and t.album_id = l.album_id

Also, I believe that third WHERE clause is more than you actually need. I think this does the trick:

select a.name, l.album, t.track_nbr, t.track_name
from track t, album l, artist a 
where t.album_id = l.album_id
and l.artist_id = a.artist_id
order by a.name, l.album, t.track_nbr;

What exactly do you mean when you say that the album table is indexed on album_id and artist_id? Are you including artist_id in the primary key of the album table? This is superfluous, as you already have a unique value in album_id.

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171401

select a.name,
    l.album,
    t.track_nbr,
    t.track_name
from track t
inner join album l on t.album_id = l.album_id
inner join artist a on l.artist_id = a.artist_id
order by a.name,
    l.album,
    t.track_nbr;

Upvotes: 1

Aziz
Aziz

Reputation: 20705

 SELECT a.name, l.album, t.track_nbr, t.track_name
 FROM artist a
 LEFT JOIN album l ON a.artist_id = l.artist_id
 LEFT JOIN track t ON t.album_id = a.album_id
 ORDER BY a.name, l.album, t.track_nbr;

Database design-related question: Why do you have artist_id in the track table? The artist_id is already defined through the album that this track belongs to.

Upvotes: 2

Related Questions