'Using SQL to join tables with a junction table

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?

mysqlsql-serverjoinjunction-table


Solution 1:[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?

Solution 2:[2]

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

Solution 3:[3]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Julien Vavasseur
Solution 2 t-clausen.dk
Solution 3 Walter Monroe

Related Questions