How do I list all fields from one table plus a random value from another table?

Question

I’m having a problem making the relationship with two tables, tb_adm_anuncios and tb_adm_anuncio_banner.

For each ad I have 4 banners, and to list these ads I need:

**Anúncio:**
-Nome do Anúncio
-Link

**Banner:**
-Imagem

The command I’m using to do this is this:

SELECT 
anuncios.id AS id_anuncio, 
anuncios.titulo_anuncio, 
banners.id AS id_banner, 
Rand(banners.imagem) AS imagem_banner 
FROM tb_adm_anuncio AS anuncios 
LEFT JOIN tb_adm_anuncio_banner AS banners ON anuncios.id = banners.fk_anuncio AND 
                                              anuncios.status = 1 
GROUP BY banners.fk_anuncio 
limit 12

When I execute this command he does not return me all the ads, and I do not know the reason.

Answer 1

see if he answers you:

SELECT
a.id as id_anuncio,
a.titulo_banner,
(SELECT 
 b.img 
 FROM tb_adm_anuncio_banner b
 WHERE b.fk_anuncio = a.id 
 ORDER BY RAND()
 LIMIT 1
) as imagem_banner
FROM tb_adm_anuncio a
WHERE a.status = 1
LIMIT 12

Answer 2

I was able to solve it, I changed it to filter GROUP BY using.id ads instead of fk_ads.

 SELECT anuncios.id AS id_anuncio, anuncios.titulo_anuncio, banners.id AS id_banner, banners.imagem AS imagem_banner 
FROM tb_adm_anuncio AS anuncios 
LEFT JOIN tb_adm_anuncio_banner AS banners 
ON anuncios.id = banners.fk_anuncio AND 
anuncios.status = 1 GROUP BY anuncios.id limit 12;