This query gives me the data that contains the values specified in the query based on counts.

Code:
SELECT id
     , GROUP_CONCAT(tmp ORDER BY tmp) tmps
  FROM 
     ( SELECT id,tmp1 tmp FROM tmptb
       UNION
       SELECT id,tmp2 tmp FROM tmptb
       UNION
       SELECT id,tmp3 tmp FROM tmptb
       UNION
       SELECT id,tmp4 tmp FROM tmptb
     ) x
 WHERE
...