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

Question

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

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

I'm having a problem when dealing with two tables, tb_adm_anuncios and tb_adm_anuncio_banner .

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

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

**Banner:**
-Imagem

The command I am 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 run this command it does not return all my ads, and I do not know why.

mysql sql join group-by

asked by anonymous 27.07.2017 / 16:33

2 answers

How to add a value to the current one in each of the rows of a mysql table? Remove undisclosed user from the database

score 1 · Answer 1

see if it suits 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

score 1 · Answer 2

I managed to resolve it, I changed it to filter the GROUP BY using the ads.id instead of fk_anuncios.

 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;