mikaeel February 2016

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 | 
+---------+------        

Answers


Kisaragi February 2016

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


amanning February 2016

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.

Post Status

Asked in February 2016
Viewed 2,715 times
Voted 4
Answered 2 times

Search




Leave an answer