Retrieve data from another mysql table and quantity

Question

I’m having a lot of doubts about Mysql when I have to use INNER JOIN to retrieve data from other tables.

I have 3 tables:

MEDIA

id, user_id, title, Description

COMMENTS

id, user_id, media_id, comment

MEDIA_LIKES

id, user_id, media_id

The MEDIA table is the main one, it contains the id do post and id do usuário, then I have the comments table where you have the comments posted in certain posts and then the like also given in these posts.

My big doubt is like through a Query in Mysql I can get the table data MEDIA, the amount of comments and likes data for given media_id?

Answer 1

your query will be like this:

SELECT
  (SELECT COUNT(*) FROM comments WHERE media_id='214') as comentarios, 
  (SELECT COUNT(*) FROM media_likes WHERE media_id='214') as likes,
  a.*
from media a where a.id='214';

This if you want to pull only 1 id now if you want to everyone just take the Where.

---OBS pos Edit--- where ta 214 vc put your $var

SS

Answer 2

I don’t know, but I think it would be that way:

SELECT media.id, 
       media.user_id, 
       media.title, 
       media.description, 
       count(comments.id) quantidade_comentarios, 
       count(media_likes.id) quantidade_likes
FROM media_likes
INNER JOIN comments.user_id=media_likes.user_id AND comments.media_id=media_likes.media_id
INNER JOIN media.id=comments.media_id AND media.user_id=comments.user_id
WHERE media.id=10;
GROUP BY media.id, media.user_id, media.title, media.description

where WHERE media.id=10 in place of 10 the value you want filter.