CODEWITHSUNDEEP
mysqlsql
Jimit
Jimit

Reputation: 2259

SQL query problem

SELECT *
FROM `friends` AS Friend
WHERE (
  (
    (
      Friend.user_id =1618
      AND Friend.profile_id =290
    )
    OR (
      Friend.to_user_id =1618
      AND Friend.to_profile_id =290
    )
  )
  AND (
    (
      Friend.user_id !=1817
      AND Friend.profile_id !=393
    )
    OR (
      Friend.to_user_id !=1817
      AND Friend.to_profile_id !=393
    )
  )
)

I want those records Which are having ((user_id = 1618 and profile_id = 290) OR (to_user_id = 1618 and to_profile_id = 290)) but I don't include those records which are having ((user_id = 1817 and profile_id = 393) OR (to_user_id != 1817 AND to_profile_id !=393

Above is not giving output as I want.

Please help me to write query for same. Thanks in advanced.

Upvotes: 0

Views: 92

Answers (2)

MatBailie
MatBailie

Reputation: 86798

Your "not" part of the condition (1817,393) is possibly route of your problem.

(user_id != 1817 AND profile_id !=393) OR (to_user_id != 1817 AND to_profile_id !=393)

For example; If user_id and profile_id don't match (1817,393) then the OR means the to_ parts don't ever need checking. The left hand side of the OR checks out fine, it doesn't matter what happens on the right.

You can reform that as follows (assuming I understand you correctly)...

NOT ((user_id=1817 AND profile_id=393) OR (to_user_id=1817 AND to_profile_id=393))

Some people (like me) keep trying to push the manipulation to avoid the use of the NOT and save that tiny tiny piece of processing. Trust me, it's not worth it :)

SELECT *
FROM `friends` AS Friend
WHERE
 (
  (Friend.user_id    = 1618 AND Friend.profile_id    = 290)
  OR
  (Friend.to_user_id = 1618 AND Friend.to_profile_id = 290)
 )
 AND
 NOT
 (
  (Friend.user_id    = 1817 AND Friend.profile_id    = 393)
  OR
  (Friend.to_user_id = 1817 AND Friend.to_profile_id = 393)
  )
 )

Upvotes: 3

Sascha Galley
Sascha Galley

Reputation: 16091

try the following: the ! will negate the whole second block

SELECT *
FROM `friends` AS Friend
WHERE
    ((Friend.user_id = 1618 AND Friend.profile_id = 290)
        OR (Friend.to_user_id = 1618 AND Friend.to_profile_id = 290))

    AND (!(Friend.user_id = 1817 AND Friend.profile_id = 393)
        OR (Friend.to_user_id = 1817 AND Friend.to_profile_id = 393))

Upvotes: 2

Related Questions