CODEWITHSUNDEEP
sql
Nelson
Nelson

Reputation: 65

SQL Combine Three tables without common Foreign Keys

I was looking to combine three (or more) tables using SQL Server 2016. It is a music database and I am able to combine the first two without issue. Here are the two tables I am able to combine:

Artist
 ArtistID (pk)
 ArtName

Song
 SongID (pk)
 SongTitle

ArtistSong
 ArtSongID (pk)
 ArtistID (fk)
 SongID (fk)

Here is the code snippet I have created to join both tables

SELECT ArtName, SongTitle
FROM ArtistSong aso
JOIN Artist a ON aso.ArtistID = a.ArtistID
JOIN Song s ON aso.SongID = s.SongID;

I was hoping to add a third table, or maybe a fourth that does not share a foreign key with the other tables. I would like to add Album Title to the query. Here is the table for Album

Album
 AlbumID (pk)
 AlbumTitle (fk)

Should I add Album to the junction table or is there another method I should consider.

Below is an image to the ERD

enter image description here

Any help is greatly appreciated. Thank you

Upvotes: 0

Views: 31

Answers (1)

muratgu
muratgu

Reputation: 7311

How about:

...
JOIN Album_Song abs ON abs.SongID = s.SongID
JOIN Album ab ON ab.AlbumID = abs.AlbumID
...

Upvotes: 1

Related Questions