Jimmy Adaro Jimmy Adaro - 2 months ago 9
MySQL Question

Solved - Display product OR collection

I've tried to solve this for 2 days without success.

I'm doing a products system where a product can be included inside a collection.

In products' page I show all the products, BUT if some product is INSIDE a collection, I need to display only the collection name and NOT the product itself. I mean, hide the products who are inside a collection AND show the collection ONCE while also showing the others products (w/o collection).

TABLE 1: colecciones ("collections" in Spanish) -
Where I store the ID and name of the collection.

CREATE TABLE `colecciones` (
`id` INT(255) NOT NULL AUTO_INCREMENT ,
`nombre` VARCHAR(100) NOT NULL ,
PRIMARY KEY (`id`)
);

INSERT INTO `colecciones`
(`nombre`)
VALUES
('dulce'),('salado'),('agrio')
;


http://sqlfiddle.com/#!9/8534e/1/0

TABLE 2: productos ("products") -
Where I store the products and, if it is inside a collection, the collection ID (column "coleccion"). If not, the value is 0.

CREATE TABLE `productos` (
`id` INT(255) NOT NULL AUTO_INCREMENT ,
`nombre` VARCHAR(100) NOT NULL ,
`coleccion` INT(255) NOT NULL ,
PRIMARY KEY (`id`)
);

INSERT INTO `productos`
(`nombre`, `coleccion`)
VALUES
('manzana',1),('salmon',2),('sandia',0),('sopa',3),('pera',1),('agua',0)
;


http://sqlfiddle.com/#!9/6aef2/1/0

Looking at the example, the product with name "manzana" (apple) is part of collection "dulce" (sweet), and the product "agua" (water) isn't part of any collection.

What I expect to show is this:

<h1>My products</h1>

dulce /* (ID 1 table `colecciones`) because I have products with "coleccion = 1" */
salado /* (ID 2 table `colecciones`) because I have products with "coleccion = 2" */
sandia /* the product itself, not collection */
agrio /* (ID 3 table `colecciones`) because I have products with "coleccion = 3" */
agua /* the product itself, not collection */


I'm stuck with this. How can I display the data in this way with PHP?

Thanks a lot.

// EDIT

I need to order like productos.id DESC

// EDIT 2

I accomplished the order that I wanted adding at the end:

ORDER BY c.`id` DESC


So now is:

SELECT (case when c.nombre is not null then c.nombre else p.nombre end) as nombre
FROM `productos` p left join `productos_colecciones` c on p.coleccion = c.id
GROUP BY nombre, (c.nombre is not null)
ORDER BY p.`id` DESC


Many thanks to @gordon-linoff.
The query is now working as expected.

Still have some issues showing the data with PHP. I will keep trying.

// EDIT 3 - SOLVED

Yay! I have managed the way for show the data with PHP without troubles.

Thanks.

Answer

I think an aggregation will do what you want:

select (case when c.name is not null then c.name else p.name end) as name
from productos p left join
     colecciones c
     on p.coleccion = c.id
group by name, (c.name is not null);

The second condition in the group by just ensures that you get two rows when the product and coleccion have the same name.

I also think this will work for your purposes:

select p.name from productos where coleccion = 0
union all
select c.name from colecciones;

You seem to want all products not in a collection plus the collection names (which is what this query does).

EDIT:

I'm not sure which id you want, but you could do:

select p.name, p.id from productos where coleccion = 0
union all
select c.name, c.id from colecciones
order by id desc;