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?

There is nothing wrong with your query. It is giving you exactly what it should. Your data is wrong, there is no way that relations can be returned with your query if they didn't exists. Is there any field in any table that says "this is the correct band" or "actual band". Your relation is N->M so a musician can be at more than one band at once. — Jorge Campos, Nov 30 '15 at 12:40
@Strawberry oooh, you are right, I'm wrong! Missed that wrong join! I just assumed that it was band with musician_band! Thanks!! — Jorge Campos, Nov 30 '15 at 14:04

score 4 · Accepted Answer · answered Nov 30 '15 at 12:58

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?

score 3 · Answer 2 · answered Nov 30 '15 at 12:51

You have not joined your tables correct

JOIN band ON band.bandID = band.bandID

Will get 1 row for EACH row in band.

Try this instead:

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

You may even be able to remove the keyword DISTINCT with this solution

score 0 · Answer 3 · answered Apr 26 '22 at 22:54

Assuming you want to include solo musicians:

select fName, bandName
from musician m
  left join musician_band mb on mb.musicianID = m.musicianID
  inner join band b on b.bandID = mb.bandID
order by fName, bandName

Even if you keep the inner join instead of left join, conceptually it's still ideal for the query to begin with the musician table, not musician_band. If you wanted musicians per band, the table order would be reversed. Either way there's no logic in starting with the junction table.

Multiple rows per musician is the expected result.

If you want this instead:

fName         bandName
musician1     band1, band2, band3
musician2     band1, band2, band3

That requires a stored procedure or your app's code looping the query to build your desired data structure for output to the user.

Using SQL to join tables with a junction table

3 Answers3