Mysql SELECT with JOIN and LIMIT in child table

Asked

Viewed 561 times

1

I have a table catalogo related to another table imagens. Each record in catalogo has N images.

I need that in a SELECT with INNER JOIN imagens, only 3 images are returned for each table row catalogo.

I thought I’d settle the matter with something like this: 

SELECT c.`cat_id` as id, c.`cat_nome_fantasia` as empresa, i.img_nome as imagem FROM `catalogo` c 
LEFT JOIN (SELECT * FROM imagens WHERE fk_catalogo_id=**c.cat_id** LIMIT 3) i ON c.cat_id=i.fk_catalogo_id

But the column cat_id in the subquery (WHERE fk_catalogo_id=c.cat_id ) is not recognized.

Do you know if there’s any way to change that? Like making the column Global.

So I tried:

SELECT (**@valor_id:=** c.`cat_id`) as id, c.`cat_nome_fantasia` as empresa, i.img_nome as imagem FROM `catalogo` c 
LEFT JOIN (SELECT * FROM imagens WHERE fk_catalogo_id=**@valor_id** LIMIT 3) i ON c.cat_id=i.fk_catalogo_id

But the @valor_id seems not to have been set as no records returned.

Finally I tried to set the variable:

SET @valor_id:=1;
SELECT (**@valor_id:=** c.`cat_id`) as id, c.`cat_nome_fantasia` as empresa, i.img_nome as imagem FROM `catalogo` c 
LEFT JOIN (SELECT * FROM imagens WHERE fk_catalogo_id=**@valor_id** LIMIT 3) i ON c.cat_id=i.fk_catalogo_id

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

But, got stuck to manually set the @valor_id, ie, just search the images of the id set.

Does anyone have any idea how to solve?

mysql join inner select-limit

  • At first SELECT has c.cat_id as id. From there, you should use the alias (id) further on. Then it would be: ... WHERE fk_catalogo_id=id LIMIT 3) i ON id=i.fk_catalogo_id

  • Hi Rene, first thank you for the collaboration. I tried but still gives the error type "column (id) not found in the WHERE clause". I tried using alias (id) alone and with c.id. The field (id) is only not recognized in the SELECT WHERE. Outside the subquery it is usually recognized using or not the alias. Thank you

1 answer

1

Personal good I got a simple solution, which is not exactly what I was looking for, but can break a branch.
Remains open the search for a solution using (LIMIT) within the JOIN.

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.
Goal: Return only 5 images for each property in one SELECT with JOIN.

Interim solution:
Have in the table images, besides the fields as (id, fk_id_imovel, name_img, directory), an ordem_imgs field that should be populated with sequence numbers starting in 1.
So SELECT would look something like:

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

Browser other questions tagged mysql join inner select-limit

You are not signed in. Login or sign up in order to post.