SQL JOIN two tables and return corresponding like

user_15264437 0
Viewed 38

this is my first post. In a couple years of playing around, so far I have been able to find solutions to problems but I am finally stuck, mostly because I think my wording and lexicon is wrong but I digress.

I have two tables lets say tweets and likes ..

Likes

Liker | like_id | tweet_id

C |       2 |      | 27
  C |       3 |      | 1
  C |       4 |      | 2
  C |       6 |      |  22
  D |       7 |      | 3
  D |       8 |      | 27
  D |       9 |      | 22

Tweets

Tweeter | Tweet_id | 
    A |       1 |    
       A|       2 |      
      A |       3 |      
      B |       4 |      
      B |       22|      
      B |       23|      
      B |       24|

When I look at a users page I want to see all their tweets and then also if I have liked any of them it would be nice to see this, so a thumbs up or whatever.

So If I user C look at user B's feed it should show me the following when I join the tables

Tweets for user A    

Tweeter | Tweet_id | liked_by_me      
      B |       4 |      
      B |       22 |      YES
      B |       23 |      
      B |       24 |

I thought I was able to figure it out but the issue was quite obvious when tweets have more likes than just mine. This is what I have tired

SELECT * FROM tweets LEFT OUTER JOIN likes ON (tweets.tweet_id = likes.p_id) WHERE tweets.user= B AND (likes.likedby is NULL or likes.likedby= C)

But If someone else liked the the tweet other than C the full list doesn't come back...

Thank-you in advance

join postgresql sql database
2 Answers

When doing an outer join, put the constraining condition (likedby = 'C') inside of the on of the join. If you apply that condition in the where, then the row will be dropped from the result set.

SELECT tweets.tweeter, tweets.tweet_id, 
       case 
         when likes.like_id is not null then 'YES'
       end as liked_by_me
  FROM tweets 
       LEFT JOIN likes 
         ON likes.p_id = tweets.tweet_id
        AND tweets.user = 'B'
        AND likes.likedby = 'C';
user_138083190

When using a LEFT JOIN, conditions on the first table go in the WHERE clause. Conditions on the second (and subsequent tables in a chain) go in the ON clause. So you appear to want:

SELECT t.tweeter, t.tweet_id, 
       (case when l.like_id is not null then 'YES'
        end) as liked_by_me
FROM tweets t LEFT JOIN
     likes l
     ON l.p_id = t.tweet_id AND
        l.likedby = 'C'
WHERE t.user = 'B';

The reason is simple. A LEFT JOIN keeps all rows in the first table regardless of the outcome of the ON clause. Then matching columns from the second table are included, with NULL values unless the ON evaluates to true.

A WHERE conditions on those columns would typically filter out non-matching rows.

Note that I also added table aliases so the query is easier to write and to read.

user_11440350
Related