I need the SQL query not to return rows with fields null
when the field productos.id
does not exist in the table.
The SQL query is as follows:
SELECT productos.id,productos.nombre, productos.descripcion, productos.precio, tipos.nombre AS tipo_nombre, tipos.id as id_tipo,categorias.id as categoria_id, categorias.nombre as categoria_nombre, GROUP_CONCAT(DISTINCT( detalle_productos.dimensiones)) AS dimensiones
FROM productos
INNER JOIN tipos ON productos.id_tipo = tipos.id
INNER JOIN detalle_productos ON productos.id = detalle_productos.id_producto
INNER JOIN categorias on tipos.id_categoria=categorias.id
WHERE
productos.id =1
If producto.id
it exists it returns what was requested (it currently works). In the case that it does not exist, it is returning 1 row with all the fields null
and I would like that if it does not exist, it does not return any row.
If I try with it, 1
it returns the data, since it exists in the table productos
, but if I try with the id, 700000
it returns 1 row with all the fields null
and I need that if it does not exist, it does not return any row.
This is the table definition:
CREATE TABLE productos ( id int PRIMARY KEY NOT null AUTO_INCREMENT,
nombre varchar (150) not null, descripcion varchar (10000) not null,
precio int not null, dimension varchar (500), id_tipo int not null,
FOREIGN KEY (id_tipo) REFERENCES tipos(id) )
create table categorias ( id int PRIMARY KEY not null AUTO_INCREMENT,
nombre varchar (50) not null UNIQUE )
CREATE TABLE tipos( id int PRIMARY KEY NOT null AUTO_INCREMENT, nombre
varchar (50) UNIQUE, id_categoria int not null, FOREIGN KEY
(id_categoria) REFERENCES categorias(id)
CREATE TABLE detalle_productos( id int PRIMARY KEY AUTO_INCREMENT NOT
null, id_producto int not null, color varchar(100) not null,
dimensiones varchar(500) not null, stock int not null, color_type
varchar (500) not null, imagen varchar(5000) not null,
imagen_Secondary varchar(5000) not null, html_color varchar(50) not
null, FOREIGN KEY (id_producto) REFERENCES productos(id) )
Since you are using a
GROUP_CONCAT
without aGROUP BY
you are supposed to do a grouping of all the results of the query (even if there are none), so you are "forcing" it to return at least one record with the result of that grouping.You can see this behavior in this online example:
Explanation of operation detailed in the manual:
In Spanish:
The latter is what is happening. From the full result of the query there is no non- result
NULL
.On the contrary, if we use a
GROUP BY
in the appropriate field (so that it performs a functional grouping of data), if no record is found, no results will appear:I have made a grouping by
categorias.id
to avoid hiding values in thoseJOIN
after the tabledetalle_productos
.