MySQL NOT IN from another column in the same table

Question

I want to run a mysql query to select all rows from the films table, where the value of the title column does not exist anywhere in all the values ​​of another column ( collection ).

Here is a simplified version of my table of contents:

 mysql> select * from films; +----+--------------+--------------+ | id | title | collection | +----+--------------+--------------+ | 1 | Collection 1 | NULL | | 2 | Film 1 | NULL | | 3 | Film 2 | Collection 1 | +----+--------------+--------------+ 

Here is my request:

 mysql> SELECT * FROM films WHERE title NOT IN (SELECT collection FROM films); Empty set (0.00 sec) 

In this example, I would like to select rows with the names Film 1 and Film 2 , but my query does not return rows.

Here is the table structure:

 CREATE TABLE `films` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(200) NOT NULL DEFAULT '', `collection` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM; 

Answer 1

 SELECT * FROM films WHERE title NOT IN (SELECT collection FROM films where collection is not null); 

SQLFiddle: http://sqlfiddle.com/#!2/76278/1

Answer 2

Have you tried using NOT EXISTS :

 SELECT * FROM films f1 WHERE NOT EXISTS (SELECT collection FROM films f2 WHERE f1.title = f2.collection); 

See SQL Fiddle with Demo

If you want to use IN , you need to look for NOT NULL values:

 SELECT * FROM films WHERE title NOT IN (SELECT collection FROM films WHERE collection is not null); 

See SQL Fiddle with Demo

Result for both:

 | ID | TITLE | COLLECTION | ------------------------------ | 2 | Film 1 | (null) | | 3 | Film 2 | Collection 1 | 

The problem with your current request is that - theft from @Quassnoi answers here :

Both IN and NOT IN return NULL , which is not an acceptable condition for the WHERE .

Since the NULL value NULL returned by your subquery, you want to specifically exclude it.

Answer 3

Try the following:

• SQLFIDDLE DEMO

Query:

 select a.id, a.planid from one a left join one b on a.planid <> b.iid where not (b.iid is null) group by b.id ; 

Results: Based on the table of examples that I used.

 ID PLANID t15 1 j18 2 

EDIT ADD: HERE WITH OP DIAGRAM

 select b.id, b.title from opschema b inner join opschema a on b.title <> a.collection or b.collection <> a.title group by b.id ; 

OP SHCEMA SQLFIDDLE DEMO

 ID TITLE 2 Film 1 3 Film 2 

Answer 4

Another option using an external connection

 SELECT f.* FROM films f LEFT OUTER JOIN films ff ON f.title = ff.collection WHERE ff.collection IS NULL 

Answer 5

 CREATE TABLE IF NOT EXISTS `reservation_tables` ( `res_table_id` int(10) NOT NULL AUTO_INCREMENT, `res_table_name` int(10) NOT NULL, `date_time` varchar(20) NOT NULL, `partyhall_id` int(10) NOT NULL, `flag` enum('0','1') NOT NULL DEFAULT '0', PRIMARY KEY (`res_table_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ; INSERT INTO `reservation_tables` (`res_table_id`, `res_table_name`, `date_time`, `partyhall_id`, `flag`) VALUES (1, 1, '2014-08-17 12:00 am', 7, '1'), (2, 2, '2014-08-17 12:00 am', 7, '1'), (3, 3, '2014-08-18 12:00 am', 8, '1'), (4, 4, '2014-08-18 12:00 am', 8, '1'), (5, 1, '2014-08-25 12:00 am', 12, '1'), (6, 2, '2014-08-25 12:00 am', 12, '1'), (7, 3, '2014-08-20 12:00 am', 23, '1'), (8, 4, '2014-08-20 12:00 am', 23, '1'); 

Ι you had to choose an accessible table name for matching date_time

Example select available table_name where date_time = 2014-08-18 12:00 am.

solution request: im sure this works well

 SELECT distinct res_table_name FROM reservation_tables WHERE `res_table_name` NOT IN (SELECT `res_table_name` FROM reservation_tables where `date_time` = '2014-08-17 12:00 am')