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,sotheWHERE
wouldlooksomethinglike:
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:/p>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
IfIuseSELECT
tosearchfor"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);