How to filter a SELECT with n:m and bring all characteristics?

Question

I’m using n:m to create a system of attributes/characteristics for certain products, so I can reuse the same attributes for more than one product, the table is something like:

When I run a SELECT like this:

SELECT
    PROD.id         AS PRODUTO_ID,
    PROD.name       AS PRODUTO_NOME,
    PROD.price      AS PRODUTO_PRECO,
    ATTR.id         AS ATRIBUTO_ID,
    ATTR.attribute  AS ATRIBUTO_NOME
FROM
    products PROD,
    attributes ATTR,
    products_has_attributes PHA
WHERE
    PROD.id = PHA.products_id AND ATTR.id = PHA.attributes_id

He returns it to me:

PRODUTO_ID | PRODUTO_NOME | PRODUTO_PRECO | ATRIBUTO_ID  | ATRIBUTO_NOME
---------------------------------------------------------------------------
 1         |  hb20        |  29000.00     |  1           |  ar condicionado
 1         |  hb20        |  29000.00     |  2           |  teto solar
 2         |  onix        |  350000.00    |  2           |  teto solar
 3         |  hilux       |  80000.00     |  1           |  ar condicionado
 3         |  hilux       |  80000.00     |  3           |  banco couro
 3         |  hilux       |  80000.00     |  4           |  novo

I want to consult for example all cars/products that have air conditioning, then the WHERE would be something like:

WHERE
    PROD.id = PHA.products_id AND ATTR.id = PHA.attributes_id
    AND
    ATTR.attribute = 'teto solar'

It returns this:

PRODUTO_ID | PRODUTO_NOME | PRODUTO_PRECO | ATRIBUTO_ID  | ATRIBUTO_NOME
---------------------------------------------------------------------------
 1         |  hb20        |  29000.00     |  2           |  teto solar
 2         |  onix        |  350000.00    |  2           |  teto solar

So my query found only the cars that have solar roof, but I would like to get all attributes of the cars/products found at the same time I do the query/filtering, ie I would like to return something like:

PRODUTO_ID | PRODUTO_NOME | PRODUTO_PRECO | ATRIBUTO_ID  | ATRIBUTO_NOME
---------------------------------------------------------------------------
 1         |  hb20        |  29000.00     |  1           |  ar condicionado
 1         |  hb20        |  29000.00     |  2           |  teto solar
 2         |  onix        |  350000.00    |  2           |  teto solar

If I use SELECT to search for "air conditioning" and "new":

WHERE
    PROD.id = PHA.products_id AND ATTR.id = PHA.attributes_id
    AND
    ATTR.attribute = 'sunroof' AND ATTR.attribute = 'new'

I need you to return something like:

PRODUCT_ID | PRODUCT_NAME | PRODUCT_PRICE | ATTR_ID      | ATTR_NAME
---------------------------------------------------------------------------
 3         |  hilux       |  80000.00     |  1           |  ar condicionado
 3         |  hilux       |  80000.00     |  3           |  banco couro
 3         |  hilux       |  80000.00     |  4           |  novo

Structure used in the example:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

CREATE TABLE IF NOT EXISTS `mydb`.`products` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `price` DECIMAL (10,2),
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`attributes` (
  `id` INT NULL AUTO_INCREMENT,
  `attribute` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`products_has_attributes` (
  `products_id` INT(11) NOT NULL,
  `attributes_id` INT NOT NULL,
  PRIMARY KEY (`products_id`, `attributes_id`),
  INDEX `fk_products_has_attributes_attributes1_idx` (`attributes_id` ASC),
  INDEX `fk_products_has_attributes_products_idx` (`products_id` ASC),
  CONSTRAINT `fk_products_has_attributes_products`
    FOREIGN KEY (`products_id`)
    REFERENCES `mydb`.`products` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_products_has_attributes_attributes1`
    FOREIGN KEY (`attributes_id`)
    REFERENCES `mydb`.`attributes` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

INSERT INTO `attributes` (`id`, `attribute`) VALUES
(1, 'ar condicionado'),
(2, 'teto solar'),
(4, 'banco couro'),
(5, 'novo');

INSERT INTO `products` (`id`, `name`, `price`) VALUES
(1, 'hb20', '29000.00'),
(2, 'onix', '350000.00'),
(3, 'hilux', '80000.00');

INSERT INTO `products_has_attributes` (`products_id`, `attributes_id`) VALUES
(1, 1),
(3, 1),
(1, 2),
(3, 2),
(3, 4),
(3, 5);

Answer 1

Select over the link table between products and attributes so as to repeat a product as many times as it has different attributes.

The filter that decides which product the attributes will be brought from is in the subquery, which in turn is filtered by the main query.

SELECT
    products.id         AS PRODUTO_ID,
    products.name       AS PRODUTO_NOME,
    products.price      AS PRODUTO_PRECO,
    attributes.id         AS ATRIBUTO_ID,
    attributes.attribute  AS ATRIBUTO_NOME
FROM
    products_has_attributes PHA
    JOIN attributes ON attributes.id = PHA.attributes_id
    JOIN products ON products.id = PHA.products_id
WHERE
    EXISTS
        (
            SELECT 
                products_has_attributes.products_id
            FROM
                products_has_attributes 
                JOIN attributes ON attributes.id = products_has_attributes.attributes_id
            WHERE 
                products_has_attributes.products_id = PHA.products_id
                AND attributes.attribute = 'teto solar'
        )

See working on SQL Fiddle.

Editing: The query below also meets your new requirement, which is: given two attributes as parameter, return all attributes of each product that has at least these two parameters.

SELECT
    products.id         AS PRODUTO_ID,
    products.name       AS PRODUTO_NOME,
    products.price      AS PRODUTO_PRECO,
    attributes.id         AS ATRIBUTO_ID,
    attributes.attribute  AS ATRIBUTO_NOME
FROM
    products_has_attributes PHA
    JOIN attributes ON attributes.id = PHA.attributes_id
    JOIN products ON products.id = PHA.products_id
WHERE
    (
        SELECT 
            COUNT(*)
        FROM
            products_has_attributes 
            JOIN attributes ON attributes.id = products_has_attributes.attributes_id
        WHERE 
            products_has_attributes.products_id = PHA.products_id
            AND 
                (
                    attributes.attribute = 'teto solar'
                    OR attributes.attribute = 'ar condicionado'
                )
    ) = 2

In this new query, each new parameter is connected by OR in the subquery and the COUNT of the subquery has to return as many records as there are parameters. Note that logic works for any number of parameters - just add one or and adjust the comparison ("= X" where X is the number of parameters).