CODEWITHSUNDEEP
mysqlselectcomments
user1319203
user1319203

Reputation:

Select all comments from friends - Mysql Query

I have a comments and friends table in mySQL database.

The comments table contains the following columns:

member_id, comment_id, comment, video_id

The friends table contains the following columbs:

member_id, member_id_friend

Is there a query to select all comments from your friends in a specific video id?

Thank you!

Upvotes: 0

Views: 397

Answers (2)

Adam Sweeney
Adam Sweeney

Reputation: 386

select comment 
from comments 
join friends (on comments.member_id = friends.member_id) 
where member_id_friend = <id number> & video_id = <video id>;

Upvotes: 0

Benjamin Cox
Benjamin Cox

Reputation: 6120

When you create a table like friends, it's called a "join table". These are used for many-to-many relationships between items. The way your particular schema is set up, the friends table is a join table implementing the many-to-many relationship between members and other members (their friends), so some would call it a "self-join table".

Tables like this are called join tables because the way to get relevant data from them is to "join" them to other tables. This is because they don't, themselves, contain any real data to speak of.

If you're trying to get the collection associated with an individual in a many-to-many relationship, you typically use an "inner join", which restricts results from one table to records which have a match in the join table. In this case, it'd look like: 

select * from comments c
inner join friends f on c.member_id = f.member_id
where f.member_id_friend = [current user ID]
  and c.video_id = [desired video ID]

Let us know how that works for you.

Upvotes: 1

Related Questions