Hello, is it possible to write several LEFT JOIN for the same table in one SQL query? For example:

"LEFT JOIN goods_attr AS ga ON ga.goods_id = g.goods_id AND ga.attr_id = 213"

one more request

 "LEFT JOIN goods_attr AS ga ON ga.goods_id = g.goods_id AND ga.attr_id = 214"
  • what for? if you only attr_id different? write attr_id in (213, 214) - Gorets
  • The request has a LIMIT, with which the list of products is divided into pages. Because each product has 3 attributes - and the attributes are in a different table, LIMIT protects each attribute as a separate product, as a result, pagination does not work correctly - kolu4iy

1 answer 1

Yes, this is perfectly acceptable, as I understand in your case one product can have several attributes stored in one table, of course, you have to join it several times to get a line with the product and all the attributes. For example: a table with attributes:

 CREATE TABLE `attr` ( `id` INT AUTO_INCREMENT, `name` CHAR, `type` CHAR, PRIMARY KEY (`id`) )

table with goods:

 CREATE TABLE `goods` ( `id` INT AUTO_INCREMENT, `name` CHAR, `attr_1_id` INT, `attr_2_id` INT, PRIMARY KEY (`id`), KEY `attr_1_id` (`attr_1_id`), KEY `attr_2_id` (`attr_2_id`), )

the query will look like this:

 SELECT * FROM `goods` as `g` LEFT JOIN `attr` as `a1` ON (`a1`.`id` = `g`.`attr_1_id`) LEFT JOIN `attr` as `a2` ON (`a2`.`id` = `g`.`attr_2_id`)

Thus, for one product you have one entry in the product table and two entries in the attribute table, but in the resulting sample you get one row for each product and LIMIT in this case should work correctly. And as mentioned above, do not forget to set the indexes correctly)