Jump to content
Sign in to follow this  
Prodjex

MYSQL MySQL – find in set vs in

Recommended Posts

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×