How to do a SELECT with n:m retrieve all "attributes"?

Question

I'm using n:m relationship to add "attributes" to "products", because I want to retrieve the same attribute to more than one product.

My table:

mysql n:m

When perform a query like this:

SELECT
    PROD.id         AS PRODUCT_ID,
    PROD.name       AS PRODUCT_NAME,
    PROD.price      AS PRODUCT_PRICE,
    ATTR.id         AS ATTR_ID,
    ATTR.attribute  AS ATTR_NAME
FROM
    products PROD,
    attributes ATTR,
    products_has_attributes PHA
WHERE
    PROD.id = PHA.products_id AND ATTR.id = PHA.attributes_id

Return this:

PRODUCT_ID | PRODUCT_NAME | PRODUCT_PRICE | ATTR_ID      | ATTR_NAME
---------------------------------------------------------------------------
 1         |  i30         |  29000.00     |  1           |  a/c
 1         |  i30         |  29000.00     |  2           |  sunroof
 2         |  onix        |  350000.00    |  2           |  sunroof
 3         |  hilux       |  80000.00     |  1           |  a/c
 3         |  hilux       |  80000.00     |  3           |  leather seat
 3         |  hilux       |  80000.00     |  4           |  new

If "filter" the SELECT by products with "sunroof":

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

Return this:

PRODUCT_ID | PRODUCT_NAME | PRODUCT_PRICE | ATTR_ID      | ATTR_NAME
---------------------------------------------------------------------------
 1         |  i30         |  29000.00     |  2           |  sunroof
 2         |  onix        |  350000.00    |  2           |  sunroof

SELECT found two cars (with sunroof), but I need to return all attributes of founded products, in other worlds, I need return like this:

PRODUCT_ID | PRODUCT_NAME | PRODUCT_PRICE | ATTR_ID      | ATTR_NAME
---------------------------------------------------------------------------
 1         |  i30         |  29000.00     |  1           |  a/c
 1         |  i30         |  29000.00     |  2           |  sunroof
 2         |  onix        |  350000.00    |  2           |  sunroof

If SELECT products with "sunroof" and "new":

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

I want return this:

PRODUCT_ID | PRODUCT_NAME | PRODUCT_PRICE | ATTR_ID      | ATTR_NAME
---------------------------------------------------------------------------
 3         |  hilux       |  80000.00     |  1           |  a/c
 3         |  hilux       |  80000.00     |  3           |  leather seat
 3         |  hilux       |  80000.00     |  4           |  new

Database example (with INSERT/products):

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, 'a/c'),
(2, 'sunroof'),
(3, 'leather seat'),
(4, 'new');

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

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


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

What do you mean to "but I need to return all attributes of the same cars found"? — erhun, Jan 22 '15 at 23:34

score 2 · Answer 1 · answered Jan 22 '15 at 23:59

First, let's recast your query using 21st-century JOIN syntax. It's just easier to read.

SELECT  whatever
  FROM products PROD
  JOIN products_has_attributes PHA ON PROD.id = PHA.products_id
  JOIN attributes ATTR             ON PHA.attributes_id = ATTR.id

Second, let's create a simple query to find the PROD.id for all the items with a sunroof.

SELECT DISTINCT PHA.products_id id
  FROM attributes ATTR
  JOIN products_has_attributes PHA ON PROD.id = PHA.products_id
 WHERE ATTR.attribute = 'sunroof'

Finally, let's use that to report on those items.

SELECT  whatever
  FROM products PROD
  JOIN products_has_attributes PHA ON PROD.id = PHA.products_id
  JOIN attributes ATTR             ON PHA.attributes_id = ATTR.id
  JOIN (
             SELECT DISTINCT PHA.products_id id
               FROM attributes ATTR
               JOIN products_has_attributes PHA ON PROD.id = PHA.products_id
              WHERE ATTR.attribute = 'sunroof' 
       ) FILT ON PROD.id = FILT.id

You could also do this with a WHERE clause like this.

 ....
WHERE PROD.id IN 
      (
             SELECT DISTINCT PHA.products_id id
               FROM attributes ATTR
               JOIN products_has_attributes PHA ON PROD.id = PHA.products_id
              WHERE ATTR.attribute = 'sunroof' 
      )

This is what makes Structured Query Language structured: the ability to use both physical tables like your products and virtual tables made from other nested queries.

Great answer! But has a problem, `JOIN` or `WHERE` examples return this error: `Unknown column 'PROD.id' in 'on clause'` (mysql 5.6.10) — Protomen, Jan 23 '15 at 17:21

score 1 · Answer 2 · edited May 23 '17 at 11:50

The short answer is that there are several approaches—you can use EXISTS, IN or JOIN. Here are quick examples of each. If your filtered query that only returns 'sunroof' rows looks like:

SELECT id, name, value FROM tbl
WHERE value = 'sunroof';

Then the IN approach looks like:

SELECT id, name, value FROM tbl
WHERE id IN (SELECT id FROM tbl WHERE b.value = 'sunroof');

The EXISTS approach looks like:

SELECT t1.id, t1.name, t1.value FROM tbl AS t1
WHERE EXISTS (SELECT 1 FROM tbl WHERE value = 'sunroof' AND id = t1.id);

And the JOIN approach looks like:

SELECT id, t1.name, t1.value FROM tbl AS t1
JOIN (SELECT id FROM tbl WHERE value = 'sunroof') AS t2 USING (id);

In these examples, tbl represents the result of your joined products, attributes and product_has_attributes tables. I recommend updating your queries to use the current standard JOIN syntax rather than the old implicit style; for more information, see e.g. this Q&A.

Your choice of which approach to take in MySQL should be guided by the query execution plan. In some cases, the query optimizer will generate the exact same identical execution plan whether you use EXISTS, IN or JOIN; however, in many cases you will see two or three different plans for the three different approaches. A difference in the execution plan implies a difference in performance, so test them out and see which works best.

In casual usage I favor EXISTS for its intuitiveness—I find it the easiest to understand without having to think too much about the query. That said, the JOIN approach is probably the most robust in terms of set-based thinking and "proper" SQL.

Thanks, work fine! But if I use SELECT with `AND attributes.attribute = 'sunroof' AND attributes.attribute = 'new'` return 0 registers. How do I return results that have two or more specific attributes? — Protomen, Jan 23 '15 at 18:45
For each attribute, you can `JOIN` another inline view or add another `IN` or `EXISTS` condition (depending on which approach you chose) but there might be a more efficient way to do it with only a single inline view/condition. — Air, Jan 23 '15 at 18:54

Gervs · Answer 3 · 2015-01-23T20:45:32.457

I've you more attributes to filter, you can use GROUP BY ... HAVING

SELECT
    PROD.id         AS PRODUCT_ID,
    PROD.name       AS PRODUCT_NAME,
    PROD.price      AS PRODUCT_PRICE,
    ATTR.id         AS ATTR_ID,
    ATTR.attribute  AS ATTR_NAME
FROM
    (
    SELECT p.id, p.name, p.price
    FROM products p
    JOIN products_has_attributes pa
        ON p.id = pa.productsId
    JOIN attributes a
        ON pa.attributes_id = a.id
    WHERE a.attribute IN ('sunroof', 'new')
    GROUP BY p.id, p.name, p.price
        HAVING COUNT(*) = 2
    ) PROD
JOIN
    products_has_attributes PHA
    ON PROD.id = PHA.products_id
JOIN
    attributes ATTR
    ON PHA.attributes_id = ATTR.id

HAVING COUNT(*) = 2 will exclude all products which doesn't have both sunroof and new.

If you have to filter on column values from the products table you can add them to where clause of the PROD subquery.

Note that this may have some performance issues on large datatables, if so generate a multiple join or multiple WHERE exists as stated by AirThomas

How to do a SELECT with n:m retrieve all "attributes"?

3 Answers3