CODEWITHSUNDEEP
sqlsql-serversqlite
Johnny Metz
Johnny Metz

Reputation: 5955

sql: many to many relationship join

I'm very new to SQL so if there are multiple possibilities I'd like to see them all (and hear which possibilities are better than others). I'm using sqlite3.

I have the following 3 tables: user, channel, subscriptions

user:

user_id    name
1          Johnny
2          Stacy
3          Allana

channel:

channel_id    channel_name
1             ESPN
2             Disney

subscriptions:

user_id    channel_id
1          1
2          2
3          1
3          2

What SQL command do I need to perform to get the following table? I basically want to see who is subscribed to which channels by names (so exactly what's laid out in the subscriptions table but mapping numbers to names based on the other tables).

user_id    channel_id
Johnny     ESPN
Stacy      Disney
Allana     ESPN
Allana     Disney

I've tried the following but I'm getting nothing in the return statement:

select user.name, channel.channel_name from user, channel, subs where user.user_id == subs.user_id and channel.channel_id == subs.channel_id

Upvotes: 0

Views: 44

Answers (2)

G.Arima
G.Arima

Reputation: 1171

Try this out and let me know in case you face any difficulty. 

select a.name,c.channel_name
from
user a
left join
subscriptions b
on a.user_id = b.user_id
left join
channel c
on b.channel_id = c.channel_id;

or (in the format u asked in comments)

select u.name,c.channel_name
from
user u
left join
subscriptions s
on u.user_id = s.user_id
left join
channel c
on s.channel_id = c.channel_id;

Upvotes: 1

Kevin Postlewaite
Kevin Postlewaite

Reputation: 615

Haven't tested it but try this:

select u.name ,c.channel from user_id u inner join subscriptions s on u.user_id=s.user_id inner join channel c on s.channel_id=c.channel_id

Upvotes: 0

Related Questions