检查表mysql查询中可用的所有id值列表

Question

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 ?

Answer 1

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)