mysql MySQL – find in set vs in

[Prodjex]

These are two different commands with different outputs when added to a SQL query.

MySQL “in” command for example:

select *
        from assets a
        left outer join assetInterests ai on a.id = ai.assetID
        where orgID = 2011
        and deviceID in (11)
        and interestID IS NULL

In the table I have the following deviceID’s:

11
11
11
11,9

The result set will only return the top 3.  The 11,9 result will be missing.  Why is that? 11 is in that column.  The issue is that “in” I could say and deviceID in (1,3,11) and any column that had just the 1 or 3 or 11 will return, but any multiples will not.

For that I need to use the MySQL command find in set like this:

select *
        from assets a
        left outer join assetInterests ai on a.id = ai.assetID
        where orgID = 2011
        and find_in_set (11,deviceID)
        and interestID IS NULL

The post MySQL – find in set vs in appeared first on Kansas City Web Consulting | Kansas City Web Development.

View the full article