Doubt with inner join sql in multiple tables

Question

Doubt with inner join sql in multiple tables

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

0

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

The query I'm doing is to get the id of the ad, the Título , Quantidade 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 group by gives me this error:

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 query will appear, the result is repeated only because of the number of photos. So 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.

The query looks 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';

If I shoot the group by , it works "correctly", but as I have the ad, 1 and 2 with a photo each and ad 3 with two photos, I get 4 values , instead of returning 3 values since the intention is to group all the results of the photos by advertisement.

mysql

asked by anonymous 04.12.2017 / 13:59

3 answers

More than one cursor in procedure Mysql How to create a POST-type method in WCF (.NET) that receives a String parameter by URL and Stream by POST?

score 0 · Answer 1

@Felipe Paz ... this happens because the photo relationship with advertisement is from many to one (n: 1). In this case, you can do the following:

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 DISTINCT statement:

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;

score 0 · Answer 2

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

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

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

score 0 · 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 to copy the value, go to the my.ini file, and then add the variable sql_mode to the value you returned from the above query, removing only the ONLY_FULL_GROUP_BY , which leaves you free to make non-aggregated clusters. p>