CODEWITHSUNDEEP
phpmysqlsql-server
mikaeel
mikaeel

Reputation: 55

Checking certain row from a table which is referenced to another table?

I know that the question will not make any sense at all to what I am asking, but what I mean is that..... To make it more clear, I will illustrate my problem in examples:

I have a userpost table that contains posts from different users.

userpost table:

 +---------+--------+--------------+
 |  postId | userId | postMessage  |
 +---------+--------+--------------+
 |       1 |      3 | someText     |
 |       2 |      5 | someText     |
 |       3 |      2 | sometext     |
 |       5 |      6 | someText     |
 +---------+--------+--------------+

(!REMEMBER that userId is being referenced to users table)

I have another table called favorites where postId is being referenced from userpost table:

favorites table:

+---------+--------+
|  postId | userId |
+---------+--------+
|       1 |      5 |  
|       3 |      2 |  
+---------+--------+

What I want is to grab all the data from userpost and check if a certain userpost has been Favorited by (WHERE userId = 5) let's say

I tried using this query but that's not what I want!

    SELECT *,
    (SELECT EXISTS( SELECT * FROM `favorites` INNER JOIN `userpost` on 
    favourites.postId = userpost.postId WHERE favorites.postId = 1 
    AND   favorites.userId = 5)) AS isFavourited FROM userpost;

This is the result of the following query:

+---------+--------+-------------+--------------+
|  postId | userId | postMessage | isFavourited |
+---------+--------+-------------+--------------+
|       1 |      3 | someText    |            1 |  
|       2 |      5 | someText    |            1 | 
|       3 |      2 | someText    |            1 |  
|       5 |      3 | someText    |            1 | 
+---------+--------+-------------+--------------+

I know that I am making the mistake inside the query by using:

(WHERE favorites.postId = 1 AND favorites.userId = 5)

which does return true.

I will give you an example of what I want:

Lets say (userId = 5) wants to grab all the userpost and we must get the result below:

+---------+--------+-------------+--------------+
|  postId | userId | postMessage | isFavourited |
+---------+--------+-------------+--------------+
|       1 |      3 | someText    |            1 |  
|       2 |      5 | someText    |            0 | 
|       3 |      2 | someText    |            0 |  
|       5 |      3 | someText    |            0 | 
+---------+--------+-------------+--------------+

Upvotes: 3

Views: 61

Answers (2)

amanning
amanning

Reputation: 64

Try:

SELECT *, 
    postId IN 
        (SELECT postId FROM favourites WHERE userId = 5) 
    AS isFavourited
FROM userPost

Your query checks that a row exists where user 5 favourites post 1; not where user 5 favorites the post being selected in that row of the return.

Upvotes: 2

Kisaragi
Kisaragi

Reputation: 2218

I think you can do something like this if I understand what you're asking :

select up.*, case when f.postId is null then "0" else "1" end as isFavourited 
from userpost up
left join favourites f on f.postId  = up.postId  and f.userId = up.userId

Upvotes: 2

Related Questions