Anant Anant - 5 months ago 16
SQL Question

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, not 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).


Please let me know what mistake I did in last two queries?

Note:- I am using these query directly in
phpmyadmin

Answer

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