CODEWITHSUNDEEP
phpmysql
Death-is-the-real-truth
Death-is-the-real-truth

Reputation: 72269

Two FIND_IN_SET in a single query is not working

Below is the table structure (invite table):-

id   user_id   doc_name        discussion_topic     invited_friends  accepted_invitation 
    
1     1        IEP Form-1.docx   first topic        2,3,4             NULL

Now:

When I did query:-

SELECT * from invite WHERE  (FIND_IN_SET(2,invited_friends) > 0)

I got this record perfectly(shown above).

But when I did:

SELECT * from invite WHERE  (FIND_IN_SET(2,invited_friends) > 0) AND (FIND_IN_SET(2,accepted_invitation) =0)

I didn't get the above record, not any error too.

Also when I did:

SELECT * from invite WHERE user_id <>1 AND (FIND_IN_SET(2,invited_friends) > 0) AND (FIND_IN_SET(2,accepted_invitation) =0)

Again I didn't get the above record, or any error too.

Actually the scenario I have to check that a given id(example 2) is not equal to user_id and must be in invite_friends(never be null) and not in accepted_invitation(accepted_invitation will be null or , separated id values).

What mistake I did in the last two queries?

Note: I am using these queries directly in PHPMyAdmin

Upvotes: 3

Views: 418

Answers (3)

Saty
Saty

Reputation: 22532

To check your scenario use 

SELECT * from invite WHERE user_id <>2// check user ID not equal to 2
AND (FIND_IN_SET(2, invited_friends) > 0)// 2 present in invited_friends
AND ((FIND_IN_SET(2, accepted_invitation) = 0)|| (FIND_IN_SET(2, accepted_invitation) IS NULL))// check 2 is null and not present in accepted_invitation

Upvotes: 2

Arulkumar
Arulkumar

Reputation: 13237

Use IFNULL for the column names:

SELECT * 
FROM   invite 
WHERE  user_id <> 1 AND
      (FIND_IN_SET(2, IFNULL(invited_friends, 0)) > 0)  AND 
      (FIND_IN_SET(2, IFNULL(accepted_invitation, 0)) = 0);

Check the SQL Fiddle: http://sqlfiddle.com/#!9/98d95/9

Upvotes: 2

naveen mishra
naveen mishra

Reputation: 82

You should use this

Replace '= 0' to 'is null'

SELECT * from invite WHERE  (FIND_IN_SET(2,invited_friends) > 0) AND (FIND_IN_SET(2,accepted_invitation) is null )

Upvotes: 2

Related Questions