MySQL Query to Pull Distinct Id with Multiple WHEREs

I am having a brain freeze with what originally I thought would be a simple query.

I have a table like the following:

ID   DETAILID    NAME    
-------------------------------------------------
1    1           Sed ut perspiciatis unde
2    1           omnis iste natus error
3    1           sit voluptatem accusantium
4    1           doloremque laudantium
5    2           totam rem aperiam
6    2           labore et dolore
7    3           voluptate velit esse
8    3           occaecati cupiditate non
9    3           culpa qui officia
10   3           placeat face开发者_如何学Gore possimus
11   3           Nam libero tempore

I want to search the NAME field multiple times with LIKE '%%' (number of LIKE's will be random) but I only want to return a distinct DETAILID where the LIKE's are all found in records where the DETAILID is the same.

I am going round in circles and getting nowhere fast. Can anybody help?

This query gives you the DETAILIDs that match all the LIKE conditions

SELECT DETAILID
FROM
(
    select DETAILID, 1 as WhichMatch
    from tbl
    where NAME LIKE '%a%'
    UNION ALL
    select DETAILID, 2 as WhichMatch
    from tbl
    where NAME LIKE '%b%'
    UNION ALL
    select DETAILID, 3 as WhichMatch
    from tbl
    where NAME LIKE '%c%'
) SQ
GROUP BY DETAILID
HAVING COUNT(DISTINCT WhichMatch) = 3

Because you are looking for ALL "LIKE" values to be found, you'll need an OR between them, a COUNT(*) to see how many match, and a GROUP BY per ID, and a HAVING to match the count you expect...

select 
      DETAILID,
      count(*) RecsFound
   from 
      YourTable
   where 
          NAME LIKE '%a%'
       or NAME LIKE '%b%'
       or NAME LIKE '%c%'
       or NAME LIKE '%d%'
   group by
      DetailID
   having
      RecsFound = 4

As you mentioned, it could be a random number of "like" qualifiers and sounds like it will be a dynamically constructed SQL Query. As such, make sure your HAVING clause count matches those entries you are testing against.

EDIT - revised answer move IF() to where section

select 
      DETAILID
   from 
      YourTable
   where 
          if( NAME LIKE '%a%', 1, 0 ) 
        + if( NAME LIKE '%b%', 1, 0 ) 
        + if( NAME LIKE '%c%', 1, 0 ) 
        + if( NAME LIKE '%d%', 1, 0 ) = 4

I guess I was looking at it from a multi-rowed table where each entry was its own element you were testing for. You are looking to have all values in the same ROW matching ALL the LIKEs... Although you have accepted another answer, here's another alternative WITHOUT using all the distinct unions...

Additionally, goes through the table ONCE instead of 2, 3, 4 or more via all the unions, then having a group by... It hits the record ONCE, qualifies all 4 conditions and is DONE.

Wouldn't this work?

SELECT DISTINCT DETAILID 
FROM MyTable
WHERE NAME LIKE '%something%'
   or NAME LIKE '%sometingelse%

Well, after reading your comments on other two answers, I believe you need this -

Use AND instead of OR in the query -

SELECT DISTINCT DETAILID 
FROM MyTable
WHERE NAME LIKE '%something%'
   AND NAME LIKE '%someting1%'
   AND NAME LIKE '%someting2%'

I think the easiest solution is to do it in multiple queries. Selecting the count() of the distinct id with a WHERE and selecting the count() of the distinct id without where condition. Comparing those 2 would be a perfect way of determining wether all 'names' of 'detialids' match your LIKE's