CODEWITHSUNDEEP
mysqlsql
mvasco
mvasco

Reputation: 5101

MySQL query to get one item from a table an multiple items from another table

I have a MYSQL table called tbl_product

enter image description here

Another table called tb_opciones_productos

enter image description here

And a third table called tb_opciones

enter image description here

I need to show every item from tbl_product as follows:

enter image description here

How can I get one item from tbl_product and the needed rows from tb_opciones_producto to get the needed result?

EDIT:

This is my current query proposal:

SELECT tbl_product.*, 
GROUP_CONCAT( (SELECT CONCAT(tb_opciones.nombre, "(+$", tb_opciones.precio, ")") 
FROM tb_opciones WHERE tb_opciones.id_opcion = tb_opciones_productos.id_opcion) SEPARATOR "<br>" ) as options FROM tbl_product 
INNER JOIN tb_opciones_productos ON tbl_product.id = tb_opciones_productos.producto

Upvotes: 0

Views: 324

Answers (1)

Obzi
Obzi

Reputation: 2390

I've create a little sqlfiddle to test : http://sqlfiddle.com/#!9/fc3316/16
You can GROUP_CONCAT a sub-query. It may not be optimized, but it do the job.

PS: next time, can you provide a sample structure ?

Structure :

    CREATE TABLE IF NOT EXISTS `products` (
      `id` int(6) unsigned NOT NULL,
      `name` varchar(255) NOT NULL,
      PRIMARY KEY (`id`)
    ) DEFAULT CHARSET=utf8;
    
    INSERT INTO `products` (`id`, `name`) VALUES
      (1, 'Product Lorem'),
      (2, 'Product Ipsum');
    
    CREATE TABLE IF NOT EXISTS `products_options` (
      `id_product` int(6) unsigned NOT NULL,
      `id_option` int(6) unsigned NOT NULL,
      PRIMARY KEY (`id_product`, `id_option`)
    ) DEFAULT CHARSET=utf8;
    
    INSERT INTO `products_options` (`id_product`, `id_option`) VALUES
      (1, 1),
      (1, 2),
      (1, 3),
      (2, 3);
      
    
    CREATE TABLE IF NOT EXISTS `options` (
      `id` int(6) unsigned NOT NULL,
      `name` varchar(255) NOT NULL,
      `value` double NOT NULL,
      PRIMARY KEY (`id`)
    ) DEFAULT CHARSET=utf8;
    
    INSERT INTO `options` (`id`, `name`, `value`) VALUES
      (1, 'Option A', 42),
      (2, 'Option B', 6),
      (3, 'Option C', 12);

Request :

    SELECT products.*,
      GROUP_CONCAT(options.name, " (+$", options.value, ")" SEPARATOR "<br>")
    FROM products
      INNER JOIN products_options
        ON products.id = products_options.id_product
      INNER JOIN options
        ON products_options.id_option = options.id
    GROUP BY products.id

With your Structure, I think this one will work :

SELECT tbl_product.*,
  GROUP_CONCAT(tb_opciones.nombre, " (+$", tb_opciones.precio, ")" SEPARATOR "<br>")
FROM tbl_product
  INNER JOIN tb_opciones_productos
    ON tbl_product.id = tb_opciones_productos.producto
  INNER JOIN tb_opciones
    ON tb_opciones_productos.opcion = tb_opciones.id
GROUP BY tbl_product.id

Upvotes: 1

Related Questions