CODEWITHSUNDEEP
mysqlsql
Sebastian
Sebastian

Reputation: 3626

Which MySQL JOIN is suitable for this?

I have three tables:

  1. Users (id, email, password)
  2. Feed_items (id, title, feed_id)
  3. Subscriptions (id, user_id, feed_id)

What I'm trying to do is get all of the feed items that a user is subscribed to by mapping id in Users and feed_id in Feed_items to user_id and feed_id in Subscriptions.

Because there are three tables, I'm thrown and unsure which join(s) to use here.

Upvotes: 0

Views: 33

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

You can so inner join as

select 
u.id,
u.email,
f.title
from Subscriptions s 
inner join Users u on u.id = s.user_id
inner join Feed_items f on f.feed_id = s.feed_id

Upvotes: 2

Aioros
Aioros

Reputation: 4383

Try something like this:

SELECT *
FROM subscriptions s
INNER JOIN feed_items f ON s.feed_id = f.id
WHERE s.user_id = 'yourUserId'

Or, if you want user information as well:

SELECT *
FROM users u
INNER JOIN subscriptions s ON u.id = s.user_id
INNER JOIN feed_items f ON s.feed_id = f.id
WHERE u.id = 'yourUserId'

Upvotes: 0

evenro
evenro

Reputation: 2646

You need to join both tables,

select * from a a1, a a2, a a3 where a1.a = a2.a and a2.a = a3.a

The important thing is to keep the logic you intend:

select * from Users a,Feed_items b, Subscriptions C 
where a.id = c.user_id and b.id = c. feed_id

Upvotes: 0

Related Questions