Mysql SELECT with JOIN and LIMIT in child table

Question

Mysql SELECT with JOIN and LIMIT in child table

Navigation

#1 by (0 votes)

0

Hello, I would like the help of the collaborators. I have a table "catalog" related to another table 'images'. Each record in "catalog" has n pictures. I need in a SELECT with INNER JOIN images, only 3 images are returned for each row of the catalog table.

I've been thinking about solving the issue with something like

SELECT c. cat_id as id, c. cat_nome_fantasia the company, i.img_name the image FROM catalogo c LEFT JOIN (SELECT * FROM images WHERE fk_catalogo_id = c.cat_id LIMIT 3) i ON c.cat_id = i.fk_catalogo_id

However the cat_id column in the subquery (WHERE fk_catalogo_id = c.cat_id ) is not recognized. Do you know if there is any way to change that? Type make the Global column.

Then I tried SELECT ( @valor_id: = c. cat_id ) the id, c. cat_nome_fantasia the company, i.img_name the image FROM catalogo c LEFT JOIN (SELECT * FROM images WHERE fk_catalogo_id = @valor_id LIMIT 3) i ON c.cat_id = i.fk_catalogo_id

But @valor_id seems not to have been set because it did not return records.

Finally I tried to set the variable. SET @valor_id: = 1; SELECT ( @valor_id: = c. cat_id ) the id, c. cat_nome_fantasia the company, i.img_name the image FROM catalogo c LEFT JOIN (SELECT * FROM images WHERE fk_catalogo_id = @valor_id LIMIT 3) i ON c.cat_id = i.fk_catalogo_id

In this case it almost worked , searched for the images with LIMIT.

Does anyone have any idea how to solve it? Thanks

mysql join inner select-limit

asked by anonymous 16.04.2017 / 02:59

1 answer

FILE function in C How do I create a login screen and password in a fragment?

score 0 · Answer 1

Well folks I got a simple solution , which is not exactly what I was looking for, ( LIMIT ) within JOIN / strong>.

Situation: Use of 2 related tables 1: n where we have several properties in the first table and each property has several images in another table.
Objective: Return only 5 images for each property in a SELECT with JOIN .

Provisional solution:
Have in the table images, in addition to the fields as (id, fk_id_imovel, img_name, directory), an order_imgs field that should be populated with sequence numbers starting at 1. So in the SELECT would look something like:

SELECT i , img FROM imoveis i
LEFT JOIN images img ON img.fk_id_imovel = i.id AND img.ordem_imgs < = 5