Sora Sora - 15 days ago 7
MySQL Question

php myadmin query not returning the expected results

SELECT u.* ,
(select CASE u.ID
WHEN u.ID in (select RequestedUserID from user_requestes where userID=3) THEN 0
ELSE 1
END ) AS Accepted
FROM users u
WHERE u.ID <>3
and u.id not in (select friends.FriendID
from friends
where friends.UserID=3 or friends.FriendID=3)
order by u.Name asc


i am trying to execute this query using phpmyadmin

select RequestedUserID from user_requestes where userID=3


the above query return
79
as result

and if i execute the original query i found this

enter image description here

Accepted should be 0 and not 1

Answer

And what if you write your query like this?

SELECT
    u.*,
    CASE
        WHEN u.ID IN (select RequestedUserID from user_requestes where userID=3) THEN 0
        ELSE 1
    END AS Accepted
FROM
    users u 
WHERE
    u.ID <>3 
    and u.id not in (
        select
            friends.FriendID 
        from
            friends 
        where
            friends.UserID=3 or friends.FriendID=3
    ) 
order by
    u.Name asc
  • Don't use SELECT before CASE;
  • If you go for CASE WHEN... syntax, you must provide values and not search conditions (See MySQL documentation here)