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

Question

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

Navigation

#1 by (3 votes)

5

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 looks something like:

WhenIexecuteaSELECTlikethis:

SELECTPROD.idASPRODUTO_ID,PROD.nameASPRODUTO_NOME,PROD.priceASPRODUTO_PRECO,ATTR.idASATRIBUTO_ID,ATTR.attributeASATRIBUTO_NOMEFROMproductsPROD,attributesATTR,products_has_attributesPHAWHEREPROD.id=PHA.products_idANDATTR.id=PHA.attributes_id

Itreturnsmethis:

PRODUTO_ID|PRODUTO_NOME|PRODUTO_PRECO|ATRIBUTO_ID|ATRIBUTO_NOME---------------------------------------------------------------------------1|hb20|29000.00|1|arcondicionado1|hb20|29000.00|2|tetosolar2|onix|350000.00|2|tetosolar3|hilux|80000.00|1|arcondicionado3|hilux|80000.00|3|bancocouro3|hilux|80000.00|4|novo

Iwanttocheckforexampleallcars/productsthathaveairconditioning,sotheWHEREwouldlooksomethinglike:

WHEREPROD.id=PHA.products_idANDATTR.id=PHA.attributes_idANDATTR.attribute='tetosolar'

Itreturnsthis:

PRODUTO_ID|PRODUTO_NOME|PRODUTO_PRECO|ATRIBUTO_ID|ATRIBUTO_NOME---------------------------------------------------------------------------1|hb20|29000.00|2|tetosolar2|onix|350000.00|2|tetosolar

Somyqueryfoundonlythecarsthathavesunroof,butI'dliketogetallattributesofthecars/productsfoundatthesametimethatImakethequery/filtering,ieI'dlikesomethingtoreturnlike:

PRODUTO_ID|PRODUTO_NOME|PRODUTO_PRECO|ATRIBUTO_ID|ATRIBUTO_NOME---------------------------------------------------------------------------1|hb20|29000.00|1|arcondicionado1|hb20|29000.00|2|tetosolar2|onix|350000.00|2|tetosolar

IfIuseSELECTtosearchfor"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 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);

mysql sql

asked by anonymous 22.01.2015 / 23:33

1 answer

URLs in doctype and html tags Change pages url with ajax and load in another browser

score 3 · Accepted Answer

Make the select on the linking table between products and attributes in order to repeat a product as many times as many different attributes as it has.

The filter that decides which product the attributes will be brought to 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 in SQL Fiddle .

Edition: The following query also meets your new requirement, which is: given two attributes as a parameter, return all attributes of each product that has at least 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 subquery has to return as many records as there are parameters. Note that the logic works for any number of parameters - just add a or and adjust the comparison where is the number of parameters.