Mysql – the best way to query these tables? Inner Join

MySQL

database: showsdb

tables:

  • shows (id, date, venue, city, state, length, source, source2, rating)
  • songs (id, name)
  • shows_songs (shows_id, songs_id)

I want to be able to query the shows table and display all the songs from that show. I also want to query the songs table and display all the shows where that song appears.

For example:

shows table has 2 shows. 3 songs played at each show.

songs table has 5 songs. Some of those songs were played at show1. some at show2. some at both

show1 setlist: song1, song2, song3

show2 setlist: song2,song4, song5

Do I need a FK in one of these tables? How would that look? Or can this be done with joins? I am not too clear on that either.

Thanks for your help and suggestions

Best Answer

This would show all shows, that run that ne song.

SELECT
    s1.date
    , s1.venue, s1.city, s1.state, s1.length, s1.source, s1.source2, s1.rating,
    s3.name
FROM 
        shows s1
    INNER JOIN 
        shows_songs s2
        ON s2.shows_id = s1.id
    INNER JOIN 
        songs  s3
        ON s2.songs_id = s3.id
WHERE s3.name = "Walk like an Egyptian"

Foreign keys are needed when you want to guarantee, that in the shows_songs are only shows/songs that already exist, but usually that you will do at app level, but with a foreign key restrainto you can't enter false data.

another Option is a FOREIGN KEY with ON DELETE CASCADE for example between shows and shows_songs, this would delete an entry from shows_songs when the shows would be deleted.

But in your case you don't need a foreign key.

An INNER JOIN is then need when you need to retrieve data from different shows.name and songs.name, then you need always to join the bridge table before the second table.

And take a good look at Aggregation function

Because

SELECT
    s1.venue,s3.name
FROM 
        shows s1
    INNER JOIN 
        shows_songs s2
        ON s2.shows_id = s1.id
    INNER JOIN 
        songs  s3
        ON s2.songs_id = s3.id
WHERE s1.id = 1

Would result in

venue    name
venue1   song1
venue1   song2
venue1   song3
venue1   song4

Which could be made to a single row with GROUP_CONCAT

this is a small database, so you can test the queries and results quite fast

Related Question