2
The SQL
below returns the values correctly only if the field data_emissao
be as nonzero in my BD table (independent if the parameter cliente
was passed or not), but I wish to return the values where the fields data_emissao
are also null (if the parameter cliente
has been passed).
How should I use the logical operator OR
without failing to obey the condition in case the field data_emissao
is different from null?
$sql = " SELECT * FROM nfe WHERE (cliente LIKE :cliente OR :cliente_ IS NULL)";
$sql. = " AND (data_emissao IS NOT NULL AND data_emissao <= :dt_final
OR :dt_final_ IS NULL)";
Problem simulation:
------------------------------
-- Tabela `nfe`
------------------------------
1 | Aarco A | NULL
2 | Barco B | 13-01-2017
3 | Carco C | 14-01-2017
4 | Darco D | 14-01-2017
5 | Earco E | 15-01-2017
6 | Farco F | NULL
7 | Garco G | 16-01-2017
SQL:
SELECT * FROM nfe WHERE (cliente LIKE '%Aarco%')
AND (data_emissao IS NOT NULL AND data_emissao <= '15-01-2017')
Result obtained:
NULL | NULL | NULL
Expected result:
1 | Aarco A | NULL
Here’s how the table was created:
-- ----------------------------
-- Table structure for nfe
-- ----------------------------
DROP TABLE IF EXISTS `nfe`;
CREATE TABLE `nfe` (
`id` int(9) NOT NULL AUTO_INCREMENT,
`cliente` varchar(255) DEFAULT NULL,
`data_emissao` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of nfe_teste
-- ----------------------------
INSERT INTO `nfe` VALUES ('1', 'Aarco A', null);
INSERT INTO `nfe` VALUES ('2', 'Barco B', '13-01-2017');
INSERT INTO `nfe` VALUES ('3', 'Carco C', '14-01-2017');
INSERT INTO `nfe` VALUES ('4', 'Darco D', '14-01-2017');
INSERT INTO `nfe` VALUES ('5', 'Earco E', '15-01-2017');
INSERT INTO `nfe` VALUES ('6', 'Farco F', null);
INSERT INTO `nfe` VALUES ('7', 'Garco G', '16-01-2017');
SQL Fiddle from BD: sqlfiddle.com/#! 9/790cb5/1
SELECT * FROM nfe_teste WHERE (LIKE client '%Aarco%') AND (data_emitted IS NULL OR data_emitted <= '15-01-2017')
– Murillo Goulart
@Murillogoulart, still returning
NULL
.– lucasbento
Try using the OR operator data_outgoing IS NOT NULL OR outgoing <= '15-01-2017')
– olavooneto
Hello @olavooneto the query returned all rows of the table.
– lucasbento