Tables

CREATE TABLE `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `category` int(11) NOT NULL, `title` varchar(120) NOT NULL COMMENT 'Название', `power` decimal(4,1) NOT NULL COMMENT 'Мощность', `luminous` int(5) NOT NULL COMMENT 'Световой поток', `price` decimal(10,2) NOT NULL COMMENT 'Цена', `hash` varchar(255) DEFAULT NULL COMMENT 'Хэш картинки', `width` int(4) DEFAULT NULL COMMENT 'Длина', `height` int(4) DEFAULT NULL COMMENT 'Ширина', `depth` int(4) DEFAULT NULL COMMENT 'Толщина', `weight` decimal(7,3) DEFAULT NULL COMMENT 'Вес', PRIMARY KEY (`id`), KEY `category` (`category`), FULLTEXT KEY `title` (`title`), CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 CREATE TABLE `product_bracing` ( `id` int(11) NOT NULL AUTO_INCREMENT, `product_id` int(11) NOT NULL COMMENT 'Товар', `bracing` char(4) NOT NULL COMMENT 'Тип крепления', PRIMARY KEY (`id`), KEY `product_id` (`product_id`), CONSTRAINT `product_bracing_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=432 DEFAULT CHARSET=utf8

An example of my request

 SELECT `t`.`id` AS `t0_c0`, `t`.`category` AS `t0_c1`, `t`.`title` AS `t0_c2`, `t`.`power` AS `t0_c3`, `t`.`luminous` AS `t0_c4`, `t`.`price` AS `t0_c5`, `t`.`hash` AS `t0_c6`, `t`.`width` AS `t0_c7`, `t`.`height` AS `t0_c8`, `t`.`depth` AS `t0_c9`, `t`.`weight` AS `t0_c10`, `get_bracing`.`id` AS `t1_c0`, `get_bracing`.`product_id` AS `t1_c1`, `get_bracing`.`bracing` AS `t1_c2` FROM `products` `t` LEFT OUTER JOIN `product_bracing` `get_bracing` ON (`get_bracing`.`product_id`=`t`.`id`) WHERE (get_bracing.bracing='cons' AND get_bracing.bracing='lyra' AND category=8) ORDER BY t.id DESC

There is a products table, it has a connection to the products_bracing table - 1: M, in the products_bracing table there is a 'bracing' field that takes the value 'cons' or 'lyra'. The result of selecting all the fields displays:

 products.id | products_bracing.bracing 3 | cons 3 | lyra 4 | cons 5 | cons

how to make a request to get products where products_bracing.bracing = 'cons' and products_bracing.bracing = 'lyra' , I always get emptiness for some reason

  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

3 answers 3

To search for products simultaneously with several types of fasteners, you can glue the bracing table once and select the required types of fasteners. In this case, the number of records in the sample will correspond to the number of matched conditions. Therefore, we group the records before the product and select those for which the number of records received equals the number of specified conditions. When gluing, there is no need to use LEFT JOIN. in any case, we only need entries for which there is something in bracing.

 SELECT `t`.`id` AS `t0_c0`, `t`.`category` AS `t0_c1`, `t`.`title` AS `t0_c2`, `t`.`power` AS `t0_c3`, `t`.`luminous` AS `t0_c4`,`t`.`price` AS `t0_c5`, `t`.`hash` AS `t0_c6`, `t`.`width` AS `t0_c7`, `t`.`height` AS `t0_c8`, `t`.`depth` AS `t0_c9`, `t`.`weight` AS `t0_c10`, `get_bracing`.`id` AS `t1_c0` FROM `products` `t` JOIN `product_bracing` `get_bracing` ON `get_bracing`.`product_id`=`t`.`id` and get_bracing.bracing in('lyra','cons') WHERE category=8 GROUP BY t.id HAVING count(1)=2 ORDER BY t.id DESC
  • Exactly what is needed. Prompt what role does HAVING count (1) = 2 код play here ? - Kirill
  • @ K.Lobastov Just the most basic, counts the number of records, i.e. the number of matched conditions. So when adding types to bracing in() instead of 2, there was a count of values ​​inside in. By the way, you said you have 7 similar tables. Consider the variant with 1 table in which one field is more than "condition type" (which will denote your current table one of 8) and collect everything in it. then selecting with a similar query will just do - Mike

If I understand the task correctly, I need to give out goods that have both the type of attachment cons and the type of mount lyra
In this case, you need to change the left join to the inner join and make two joins to the products_bracing table:

 SELECT `t`.`id` AS `t0_c0`, `t`.`category` AS `t0_c1`, `t`.`title` AS `t0_c2`, `t`.`power` AS `t0_c3`, `t`.`luminous` AS `t0_c4`, `t`.`price` AS `t0_c5`, `t`.`hash` AS `t0_c6`, `t`.`width` AS `t0_c7`, `t`.`height` AS `t0_c8`, `t`.`depth` AS `t0_c9`, `t`.`weight` AS `t0_c10`, `get_bracing_cons`.`id` AS `t1_c0`, `get_bracing_cons`.`product_id` AS `t1_c1`, `get_bracing_lyra`.`bracing` AS `t1_c2` FROM `products` `t` INNER JOIN `product_bracing` `get_bracing_cons` ON (`get_bracing_cons`.`product_id`=`t`.`id` AND get_bracing_cons.bracing='cons' AND category=8) INNER JOIN `product_bracing` `get_bracing_lyra` ON (`get_bracing_lyra`.`product_id`=`t`.`id` AND get_bracing_lyra.bracing='lyra' AND category=8) ORDER BY t.id DESC
  • Cool, it works) But still the question is, without an extra join, not to solve this problem? It turns out if there are many types of fasteners, add a connection to each of them? - Kirill
  • Here I would still like to know - and what should I get in the end? :) Again, if I correctly understand the task - to get a list of fixtures that have a specific list of fixtures, then cross-join the goods to the table with this list, then left join with the attachment types by product id and attachment type, then we look at those records where there is not a single NULL in the mount ID. - Zufir
  • Well, it would be nice to normalize the table of fasteners, highlighting the directory;) - Zufir
  • There are also tables similar to product_bracing (pieces 7) where the value types can be not 2, but quite a lot. It turns out when filtering goods, we do a lot of joins, is this the norm for this task? - Kirill

there is an error

 WHERE (get_bracing.bracing='cons' AND get_bracing.bracing='lyra' AND category=8)

must be

 WHERE ((get_bracing.bracing='cons' OR get_bracing.bracing='lyra') AND category=8)

the field cannot simultaneously take 2 cons and lyra values, in order to select both, you need to use OR

even better

 SELECT `t`.`id` AS `t0_c0`, `t`.`category` AS `t0_c1`, `t`.`title` AS `t0_c2`, `t`.`power` AS `t0_c3`, `t`.`luminous` AS `t0_c4`, `t`.`price` AS `t0_c5`, `t`.`hash` AS `t0_c6`, `t`.`width` AS `t0_c7`, `t`.`height` AS `t0_c8`, `t`.`depth` AS `t0_c9`, `t`.`weight` AS `t0_c10`, `get_bracing`.`id` AS `t1_c0`, `get_bracing`.`product_id` AS `t1_c1`, `get_bracing`.`bracing` AS `t1_c2` FROM `products` `t` LEFT OUTER JOIN `product_bracing` `get_bracing` ON `get_bracing`.`product_id`=`t`.`id` AND (get_bracing.bracing='cons' OR get_bracing.bracing='lyra') WHERE category = 8 ORDER BY t.id DESC

In this example, with JOIN you already select only those records where there are values

  • I agree, the field does not simultaneously accept 2 values, but with such a request I will receive more goods, where is either 'cons' or 'lyra'? But you need to fulfill 2 conditions at once - Kirill