table

id | catid | productid | 

1     3        100

2     244      110

3     400       300   

Hi, here is my query ,

select catid from table where catid NOT IN ('3','244','5')

i have a table looking like that above ,

1- what i need ,i have to check some list of catid values , if all the list catid is available an the table will return "yes" or some "rows".

2- Otherwise will return "no" all the list catid not available ?

I had tried but its useless ?

This is not correct one for my expecting result ?

SELECT catid , IF(catid IS NOT NULL , "Yes" , "No") FROM table where catid IN ('3','244','5');

what i need , the list of cat ids are "('3','244','5')" should be available an the table if once value missing an the table will return "No"

thanks for your help ?

You can do this in multiple steps

For example : Your actual table where you need to check

SELECT * FROM emp_old;
+--------+-------+
| emp_no | name  |
+--------+-------+
|      1 | abdul |
|      2 | Jp    |
|      3 | Ankur |
+--------+-------+
3 rows in set (0.00 sec)

Insert your id's in a new table which you will check or pass in IN or NOT IN

DROP TABLE IF EXISTS InsertIds;

CREATE TABLE InsertIds(Id INT);

INSERT INTO InsertIds VALUES(1),(2),(3),(4);

SELECT * FROM InsertIds;
+------+
| Id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

Now try this one

SELECT Id , IF(emp_no IS NOT NULL , "Yes" , "No") AS "Check status" FROM InsertIds a LEFT JOIN emp_old b ON a.Id = b.emp_no ;
+------+--------------+
| Id   | Check status |
+------+--------------+
|    1 | Yes          |
|    2 | Yes          |
|    3 | Yes          |
|    4 | No           |
+------+--------------+
4 rows in set (0.00 sec)

One way to solve this is using a left outer join and aggregation:

select (case when count(distinct t.catid) = 3 then 'Yes'
             else concat('No, missing ',
                         group_concat(case when t.catid is null then vals.val end)
                        )
        end)
from (select '3' as val union all '244' union all '5'
     ) vals left outer join
     t
     on t.catid = vals.val;

The list itself is going in the first subquery as a set of rows with one value per row.

I think the simplest solution here is a combination of MySQL and PHP:

Assuming that your list of catid values contains no duplicates:

Step 1 - Use PHP to get count of all elements in the list

Step 2 - Use MySQL to get distinct count of all elements in your list:

select count(distinct catid) from table where catid IN ('3','244','5');

Step 3 Use PHP to compare count from Step 1 with count from Step 2. If they are equal then 'Yes' otherwise 'No'