CODEWITHSUNDEEP
mysql
Sora
Sora

Reputation: 2551

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

Upvotes: 0

Views: 72

Answers (3)

Rick James
Rick James

Reputation: 142228

This might work better:

SELECT  u.*,
        IF(EXISTS (
                SELECT  *
                    from  user_requestes
                    where  userID = 3
                      AND  RequestedUserID = u.ID ),
           0, 1 ) AS Accepted
    FROM  users AS u
    LEFT JOIN  friends AS f  ON f.FriendID = u.ID
    WHERE  u.ID != 3
      AND  f.FriendID IS NULL
      AND  ( f.UserID = 3  or  f.FriendID = 3 )

Note the use of EXISTS as being more efficient than IN ( SELECT ... ). Ditto for LEFT JOIN ... IS NULL.

Upvotes: 0

Fran&#231;ois Dupire
Fran&#231;ois Dupire

Reputation: 605

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)

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

There is a mistake in your case expression

CASE u.ID WHEN u.ID in (...)

reads as follows: look up u.id in the subquery. Found = true, not found = false. In MySQL true = 1 and false = 0.

CASE u.ID WHEN <either 1 or 0>

You are mistakenly comparing the user ID with the boolean result 1 or 0.

You want this instead:

SELECT 
  u.* ,
  CASE 
    WHEN u.ID in (select RequestedUserID from user_requestes where userID=3) THEN 0
    ELSE 1
  END AS Accepted
FROM ...

By the way: There is probably a semantical mistake in your friends subquery, as it is always FriendID you are returning. I suppose that should be:

and u.id not in 
(
  select case when FriendID = 3 then UserID else FriendID end
  from friends
  where UserID = 3 or FriendID = 3
)

or simply

and u.id not in (select FriendID from friends where UserID = 3)
and u.id not in (select UserID from friends where FriendID = 3)

Upvotes: 1

Related Questions