CODEWITHSUNDEEP
mysqlexists
Ariyan
Ariyan

Reputation: 15158

SQL query for check existance of some keys in table in Mysql

I have some static text keys entered as part of my app. My app also has a table of keys that can be used. I want to write a query that checks whether all of those static keys exists in the table. If some are missing, I want to know what keys they are.

Here is an example table...

+----------+
|  column  |
+----------+
|   val1   |
+----------+
|   val2   |
+----------+
|   val3   |
+----------+

In this example, I want to check which of the static values val1,val3,val4,val5 don't exists in the table. How can I write a query to achieve this?

Upvotes: 1

Views: 325

Answers (1)

Nishant
Nishant

Reputation: 55856

select key_name from keys_tab 
    where key_name not in 
    (select t2.key_name from keys_tab t2, other_tab t1 
       where t2.key_name = t1.key_name);

or

select key_name from keys_tab 
    where key_name not in 
    (select t2.key_name from keys_tab t2, other_tab t1 
       where t2.key_id = t1.key_id);

May use distinct in sub-query

select key_name from keys_tab 
    where key_name not in 
    (select distinct t2.key_name from keys_tab t2, other_tab t1 
       where t2.key_id = t1.key_id);

An Example

mysql> select * from test1;
+----------+----------------------+---------------------+-------+
| col1     | col2                 | mdate               | myrow |
+----------+----------------------+---------------------+-------+
| col1     | 1/29/2011 9:59:47 AM | 2011-02-01 11:40:07 |  NULL |
| val2     | NULL                 | 2011-02-04 22:09:11 |  NULL |
| val4     | NULL                 | 2011-02-04 22:09:15 |  NULL |
+----------+----------------------+---------------------+-------+
3 rows in set (0.00 sec)

mysql> select * from test2;
+------+------+
| col1 | col2 |
+------+------+
| val1 | val2 |
| val2 | val3 |
| val4 | val3 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from test2 where col1 not in (select t2.col1 from test2 t2, test1 t1 where t2.col1 = t1.col1);
+------+------+
| col1 | col2 |
+------+------+
| val1 | val2 |
+------+------+
1 row in set (0.00 sec)

mysql>

All you need is this

CREATE TEMPORARY TABLE key_list(key_name varchar(100));
insert into key_list values ('val1'),('val2'),('val3'),('val4'),('val5');
select key_name from key_list where key_name not in (select distinct col1 from test1);

and then use this temp table as mentioned above. I am sure there must be a better method.

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed.

see here http://dev.mysql.com/doc/refman/5.1/en/create-table.html

Upvotes: 3

Related Questions