how to write where condition for single column in joint tables?

Question

I have a query as follows

SELECT s.*
     , u.email
     , u.first_name
     , COUNT(l.tweet_id) as likes
     , l.user_tweetid 
  FROM social_tweets AS s
  JOIN users AS u 
    ON s.user_id = u.id 
  left 
  JOIN social_likes AS l 
    ON s.id = l.tweet_id 
 WHERE s.user_id IN  ($string) 
 group 
    by s.id 
 ORDER 
    BY created_date  DESC 
 LIMIT 7

Here, I am getting all IDs. However, I simply want l.user_tweetid = '246'. How can I apply the WHERE condition to only that column?

Answer 1

Write as below:

SELECT s.*, u.email, u.first_name, COUNT(l.tweet_id) as likes, l.user_tweetid 
FROM social_tweets AS s 
JOIN users AS u ON s.user_id = u.id 
LEFT JOIN social_likes AS l ON s.id = l.tweet_id AND l.user_tweetid = @YourTwiteId  
WHERE s.user_id IN ($string) 
GROUP BY s.id 
ORDER BY created_date DESC 
LIMIT 7;

Answer 2

If I understand your question correctly, for your question, you only want just one record with the specific l.user_tweetid, you should use the filtering condition in the WHERE clause instead of the JOIN clause, i.e.

SELECT s.*, u.email, u.first_name,COUNT(l.tweet_id) as likes,l.user_tweetid
FROM social_tweets AS s
         JOIN users AS u ON s.user_id = u.id 
    left JOIN social_likes AS l ON s.id=l.tweet_id 
WHERE s.user_id IN  ($string) 
    and l.user_tweetid = '246'
GROUP BY s.id 
ORDER BY created_date  DESC LIMIT 7;

You can find the difference of the two at SQL join: where clause vs. on clause