mysql 5 table select query

I have 5 tables:

1. users
2. bands_users
3. ba开发者_运维知识库nds
4. bands_gigs
5. gigs

A user can have many bands, a band can have many users A gig can have many bands, a band can have many gigs

Given the user id 1, I would like to return all the gigs with their associated band.

Any help would be appreciated.

something like this?

SELECT g.*, b.*
FROM users u 
LEFT JOIN band_users bu ON u.id = bu.uid 
LEFT JOIN bands b ON bu.bid = b.id 
LEFT JOIN bands_gigs bg ON b.id = bg.bid 
LEFT JOIN gigs g ON bg.gid = g.id
WHERE u.id = 1

You can do this in one line, but to start simple, the basic idea is that you first join users with the bands_users and bands where the user id is in both users and bands_users, and the band id is in both bands_users and bands. That will get you the list of bands a user is in. Then you join bands with bands_gigs and gigs where the band id is in both bands and bands_gigs, and the gig id is in both bands_gigs and gigs. Now you have the list of gigs for the list of bands that a user is in.

Something to start with:

SELECT band.name,
       gig.name
FROM   users
       JOIN bands_users
         ON users.name = bands_users.userName
       JOIN bands
         ON bands.name = bands_users.bandName
       JOIN bands_gigs
         ON bands.name = bands_gigs.bandName
       JOIN gigs
         ON bands_gigs.gigName = gigs.name
WHERE  users.id = 1;