Description:
Don' work:
----------
select host_name from nagios_hosts where category not in (select category_name from ncpl_categories where category_filetype='6')
order by category desc;
Works:
------
select host_name from nagios_hosts where category not in (select category_name from ncpl_categories where category_filetype='6')
order by category asc;
select host_name from nagios_hosts
where category is not null and category not in (select category_name from ncpl_categories where category_filetype='6');
Attention:
----------
When you change the order by catefory from desc to asc it works for:
insert into `nagios_hosts` (`server_name`,`category`,`host_name`) values ('SERVERNAME 1',NULL,'HOSTNAME 1');
insert into `nagios_hosts` (`server_name`,`category`,`host_name`) values ('SERVERNAME 2','CAT 2','HOSTNAME 2');
When you change the order by catefory from asc to dec it works for:
insert into `nagios_hosts` (`server_name`,`category`,`host_name`) values ('SERVERNAME 2','CAT 2','HOSTNAME 2');
insert into `nagios_hosts` (`server_name`,`category`,`host_name`) values ('SERVERNAME 1',NULL,'HOSTNAME 1');
This means that there is a difference depending
How to repeat:
/*
SQLyog Pro - MySQL GUI v5.15
Host - 5.0.41-log : Database - yves
*********************************************************************
Server version : 5.0.41-log
*/
SET NAMES utf8;
SET SQL_MODE='';
create database if not exists `yves`;
USE `yves`;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
/*Table structure for table `nagios_hosts` */
DROP TABLE IF EXISTS `nagios_hosts`;
CREATE TABLE `nagios_hosts` (
`server_name` varchar(255) NOT NULL default '',
`category` varchar(64) default NULL,
`host_name` varchar(64) NOT NULL default '',
PRIMARY KEY (`server_name`,`host_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Data for the table `nagios_hosts` */
insert into `nagios_hosts` (`server_name`,`category`,`host_name`) values ('SERVERNAME 1',NULL,'HOSTNAME 1');
insert into `nagios_hosts` (`server_name`,`category`,`host_name`) values ('SERVERNAME 2','CAT 2','HOSTNAME 2');
/*Table structure for table `ncpl_categories` */
DROP TABLE IF EXISTS `ncpl_categories`;
CREATE TABLE `ncpl_categories` (
`category_filetype` varchar(11) NOT NULL default '0',
`category_name` varchar(64) NOT NULL default '',
PRIMARY KEY (`category_filetype`,`category_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*Data for the table `ncpl_categories` */
insert into `ncpl_categories` (`category_filetype`,`category_name`) values ('6','CAT 1');
insert into `ncpl_categories` (`category_filetype`,`category_name`) values ('6','CAT 2');
SET SQL_MODE=@OLD_SQL_MODE;
Works:
------
select host_name from nagios_hosts where category not in (select category_name from ncpl_categories where category_filetype='6')
order by category asc;
select host_name from nagios_hosts
where category is not null and category not in (select category_name from ncpl_categories where category_filetype='6');
Don' work:
----------
select host_name from nagios_hosts where category not in (select category_name from ncpl_categories where category_filetype='6')
order by category desc;
Description: Don' work: ---------- select host_name from nagios_hosts where category not in (select category_name from ncpl_categories where category_filetype='6') order by category desc; Works: ------ select host_name from nagios_hosts where category not in (select category_name from ncpl_categories where category_filetype='6') order by category asc; select host_name from nagios_hosts where category is not null and category not in (select category_name from ncpl_categories where category_filetype='6'); Attention: ---------- When you change the order by catefory from desc to asc it works for: insert into `nagios_hosts` (`server_name`,`category`,`host_name`) values ('SERVERNAME 1',NULL,'HOSTNAME 1'); insert into `nagios_hosts` (`server_name`,`category`,`host_name`) values ('SERVERNAME 2','CAT 2','HOSTNAME 2'); When you change the order by catefory from asc to dec it works for: insert into `nagios_hosts` (`server_name`,`category`,`host_name`) values ('SERVERNAME 2','CAT 2','HOSTNAME 2'); insert into `nagios_hosts` (`server_name`,`category`,`host_name`) values ('SERVERNAME 1',NULL,'HOSTNAME 1'); This means that there is a difference depending How to repeat: /* SQLyog Pro - MySQL GUI v5.15 Host - 5.0.41-log : Database - yves ********************************************************************* Server version : 5.0.41-log */ SET NAMES utf8; SET SQL_MODE=''; create database if not exists `yves`; USE `yves`; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; /*Table structure for table `nagios_hosts` */ DROP TABLE IF EXISTS `nagios_hosts`; CREATE TABLE `nagios_hosts` ( `server_name` varchar(255) NOT NULL default '', `category` varchar(64) default NULL, `host_name` varchar(64) NOT NULL default '', PRIMARY KEY (`server_name`,`host_name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*Data for the table `nagios_hosts` */ insert into `nagios_hosts` (`server_name`,`category`,`host_name`) values ('SERVERNAME 1',NULL,'HOSTNAME 1'); insert into `nagios_hosts` (`server_name`,`category`,`host_name`) values ('SERVERNAME 2','CAT 2','HOSTNAME 2'); /*Table structure for table `ncpl_categories` */ DROP TABLE IF EXISTS `ncpl_categories`; CREATE TABLE `ncpl_categories` ( `category_filetype` varchar(11) NOT NULL default '0', `category_name` varchar(64) NOT NULL default '', PRIMARY KEY (`category_filetype`,`category_name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*Data for the table `ncpl_categories` */ insert into `ncpl_categories` (`category_filetype`,`category_name`) values ('6','CAT 1'); insert into `ncpl_categories` (`category_filetype`,`category_name`) values ('6','CAT 2'); SET SQL_MODE=@OLD_SQL_MODE; Works: ------ select host_name from nagios_hosts where category not in (select category_name from ncpl_categories where category_filetype='6') order by category asc; select host_name from nagios_hosts where category is not null and category not in (select category_name from ncpl_categories where category_filetype='6'); Don' work: ---------- select host_name from nagios_hosts where category not in (select category_name from ncpl_categories where category_filetype='6') order by category desc;