Hi,
I'm having a trouble with a query that will select the list of inactive records that are unique by one of the columns.
Consider the table with 3 columns:
CREATE TABLE `test`.`names` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 32 ) NOT NULL ,
`date` DATE NOT NULL
);
Dataset:
INSERT INTO `test`.`names` (`id`, `name`, `date`) VALUES (NULL, 'Jon', '2010-06-01');
INSERT INTO `test`.`names` (`id`, `name`, `date`) VALUES (NULL, 'Jon', '2010-06-10');
INSERT INTO `test`.`names` (`id`, `name`, `date`) VALUES (NULL, 'Jon', '2010-06-20');
INSERT INTO `test`.`names` (`id`, `name`, `date`) VALUES (NULL, 'Jon', '2010-06-30');
INSERT INTO `test`.`names` (`id`, `name`, `date`) VALUES (NULL, 'Jim', '2010-05-20');
INSERT INTO `test`.`names` (`id`, `name`, `date`) VALUES (NULL, 'Jim', '2010-05-30');
INSERT INTO `test`.`names` (`id`, `name`, `date`) VALUES (NULL, 'Jim', '2010-06-01');
INSERT INTO `test`.`names` (`id`, `name`, `date`) VALUES (NULL, 'Jim', '2010-06-10');
columns name and time are not necessarily unique.
the name A is considered inactive, if all the corresponding records with col *name* equal to A have their date older than some given date B. Ex.
if B = 2010-06-20, then Jim will be inactive, but Jon active.
if B = 2010-05-01, then Jim and Jon will be active.
if B = 2010-07-01, then both Jim and Jon will be inactive.
Now I need to get the list of active and inactive names from this table.
For active names it is pretty simple:
SELECT name FROM names WHERE date > *some_date* GROUP BY name
But I can not come with an elegant query for the inactive names. I already tried to nest two queries:
SELECT name FROM names
WHERE name NOT IN (SELECT name FROM names_log WHERE date > *some_date* GROUP BY name)
GROUP BY name
But this query runs extremely slow - I believe because of the nested looping or something like this.
Any ideas how to make it working?
Edited 2 time(s). Last edit at 06/27/2010 12:29PM by Anton Khodakivskiy.