Using SQL to join tables with a junction table

Question

I am trying to output data from 2 different tables in my database which are joined by a junction table.

Table 1: musician
Columns: musicianID, surname, fName

Table 2: musician_band
Columns: musicianID, bandID

Table 3: band
Columns: bandID, bandName, genre, yearFormed, origin

Just to start with I want to output:

fName, bandName

I tried using this query:

SELECT DISTINCT fName, bandName
FROM musician_band
JOIN musician ON musician.musicianID = musician_band.musicianID
JOIN band ON band.bandID = band.bandID

But instead of giving me a list of musicians and the bands that they're in, it is instead repeating the first name of the first musician in the databse and then a band name on the right, this is repeated for every band in the database, even where there shouldn't be a relationship between that particular musician and band. Like this:

fname         bandName
musician1     band1
musician1     band2
musician1     band3
musician2     band1
musician2     band2
musician2     band3

etc...

How can I correct this so that it only outputs musicians in their correct bands?

Answer 1

There is a problem with your aliases. You need distinct alias for each referenced tables in your JOIN:

This sample query would work fine:

SELECT DISTINCT fName, bandName
FROM @musician_band mb
JOIN @musician m ON m.musicianID = mb.musicianID
JOIN @band b ON mb.bandID = b.bandID

Using this sample in SQL Server:

declare @musician table(musicianID int, surname varchar(50), fName varchar(50));
insert into @musician(musicianID, surname, fName
) values
( 1, 'Pete Doherty', 'Pete Doherty')
, ( 2, 'Damon Albarn', 'Damon Albarn')

declare @musician_band table(musicianID int, bandID int);
insert into @musician_band(musicianID, bandID) values
(1, 1)
, (1, 2)
, (2, 3)
, (2, 4)

declare @band table(bandID int, bandName varchar(50), genre varchar(50), yearFormed int, origin varchar(50));
insert into @band(bandID, bandName, genre, yearFormed, origin
) values
( 1, 'The Libertines', '', 0, '')
, (2, 'Babyshambles', '', 0, '')
, (3, 'Blur', '', 0, '')
, (4, 'Gorillaz', '', 0, '')

The output is:

fName           bandName
Damon Albarn    Blur
Damon Albarn    Gorillaz
Pete Doherty    Babyshambles
Pete Doherty    The Libertines

Just replace @tablename by your own table. Is DISTINCT useful in this case?