Doubt with sql Inner Join in multiple tables

Question

I have three tables. Usuários, Anúncios and Fotos.

To query what I’m doing is to catch the id of the Ad, the Título, to Quantidade of views and the first photo of this ad from the photo table. Since a single ad can have multiple photos. So far so good, but when I put the group by give me this mistake:

1055 - Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'vivachapeco.fotosanuncio.foto' which is not functionally dependent on Columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I need to get a single photo because on the page where this will be displayed query, the result is repeated only because of the amount of photos. Then, the ad with id 1 will be repeated 5 times because it has 5 photos, when in fact, I would like to have only one photo of it.

To query is like this:

SELECT `anuncios`.`id_anuncio`, `anuncios`.`id_user`, `anuncios`.`titulo`,
       `anuncios`.`visualizacoes`, `fotosanuncio`.`foto`
FROM `anuncios`
INNER JOIN `users` 
    ON `users`.`id_user` = `anuncios`.`id_user`
INNER JOIN `fotosanuncio` 
    ON `fotosanuncio`.`id_anuncio` = `anuncios`.`id_anuncio`
    WHERE `users`.`id_user` = 3
GROUP BY `fotosanuncio`.`id_anuncio`;

Already if I take the group by, works "correctly", however, as I have the ad, 1 and 2 with a photo each and the ad 3 with two photos, returns me 4 values, instead of returning 3 values since the intention is to group all the results of the photos by ad.

Answer 1

@Felipe Paz... this happens because the relationship of photos with advertising is many to one (n:1). In this case, you can do as follows:

SELECT `anuncios`.`id_anuncio`, `anuncios`.`id_user`, 
`anuncios`.`titulo`, `anuncios`.`visualizacoes`, MAX(`fotosanuncio`.`foto`)
FROM `anuncios`
INNER JOIN `users` 
ON `users`.`id_user` = `anuncios`.`id_user`
INNER JOIN `fotosanuncio` 
ON `fotosanuncio`.`id_anuncio` = `anuncios`.`id_anuncio`
WHERE `users`.`id_user` = 3
GROUP BY `anuncios`.`id_anuncio`, `anuncios`.`id_user`, 
`anuncios`.`titulo`,`anuncios`.`visualizacoes`;

Another way would be to use the instruction DISTINCT:

SELECT DISTINCT `anuncios`.`id_anuncio`, `anuncios`.`id_user`, 
`anuncios`.`titulo`, `anuncios`.`visualizacoes`, `fotosanuncio`.`foto`
FROM `anuncios`
INNER JOIN `users` 
ON `users`.`id_user` = `anuncios`.`id_user`
INNER JOIN `fotosanuncio` 
ON `fotosanuncio`.`id_anuncio` = `anuncios`.`id_anuncio`
WHERE `users`.`id_user` = 3;

Answer 2

If you want to view the first saved photo you can use the MIN() if you wanted last inserted you can use the MAX(), note that I asked you if there was a column to identify the photos in your table fotosanuncio because we cannot use min() or max() in the column with the name or path of the photo, for being a text field may not bring the expected result in all cases.

In the example below I related the table to a selection grouping by ad, assuming that your photo table has a cooluna of id_fotos.

Sqlfiddle - Example working online

SELECT anuncios.id_anuncio
   , anuncios.id_user
   , anuncios.titulo
   , anuncios.visualizacoes
   , foto
FROM anuncios
INNER JOIN users 
    ON users.id_user = anuncios.id_user
INNER JOIN (
  SELECT 
    MIN(id_fotos)
    , foto
    , id_anuncio
  FROM fotosanuncio
  GROUP BY id_anuncio
) fotosanuncio2 
  ON fotosanuncio2.id_anuncio = anuncios.id_anuncio
group by anuncios.id_anuncio
, anuncios.id_user
, anuncios.titulo
, anuncios.visualizacoes
, fotosanuncio2.id_anuncio

Answer 3

if you execute this command: SHOW GLOBAL VARIABLES LIKE '%sql_mode%' will realize that the value has this string ONLY_FULL_GROUP_BY

what you can do is copy the value, go in the file my.ini aidicionar the variable sql_mode with the value that returned from the above query removing only the ONLY_FULL_GROUP_BY which leaves you free to make non-aggregated groupings.