0

Here in my code i can't make limit for each joined table, I see always Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INNER JOIN products_images ON products.id=products_images.product_id LIMIT 1 ' at line 4

SELECT products_images.image as products_image, products_description.name as products_name, products_details.price as products_price, products.id as products_id
FROM products
INNER JOIN products_description ON products.id=products_description.product_id LIMIT 1
INNER JOIN products_images ON products.id=products_images.product_id LIMIT 1
INNER JOIN products_details ON products.id=products_details.product_id LIMIT 1
Eddy
  • 37
  • 6
  • Possible duplicate of [MySQL JOIN with LIMIT 1 on joined table](https://stackoverflow.com/questions/6879391/mysql-join-with-limit-1-on-joined-table) – Lelio Faieta Nov 27 '18 at 18:13

1 Answers1

0

That is because that is invalid syntax. If you want to limit the rows compared from joined tables you need to replace those tables with subqueries on those tables.

Making a few assumptions about the result you want, a simple example would be replacing

INNER JOIN products_description ON products.id=products_description.product_id LIMIT 1

with

INNER JOIN (SELECT * FROM products_description AS d WHERE d.product_id = product.id ORDER BY d.id DESC LIMIT 1) AS pd

I would NOT recommend this exact subquery though as correlated subqueries (the subquery's WHERE references the outer query) are usually rather inefficient; it will be separately executed for every row in products. Usually, such subqueries should find the "first for each" including (and often GROUPing on) the id of "each" and then be JOINed on that id; as that avoids costly correlation.

Uueerdo
  • 15,723
  • 1
  • 16
  • 21