Query Mysql with 3 tables

Asked

Viewed 247 times

0

I have the following query that should bring the sum of views and last view (table sir_ter_relatorios_terrenos), sum of favourites (table sir_ter_favoritos) and the person’s name (table sir_users)

SELECT u.id as idinc, u.name as nome, sum(r.views) as totalviews, max(r.data) as ultimav, COUNT(f.status)
FROM sir_ter_relatorios_terrenos r, sir_users u
LEFT JOIN sir_ter_favoritos f ON f.idincorporadora = u.id
WHERE u.id = r.iduser
GROUP BY u.id

But when it’s time to bring the COUNT(f.status) is going wrong, instead of bringing 1 (which is the only record you have in this table) it is bringing 3, which is an item count in the sir_ter_relatorios_terrenos.

Example:

sir_ter_relatorios_terrenos
id|iduser|data      |views
1 | 771  |2018-05-29| 3
2 | 771  |2018-05-29| 1
3 | 778  |2018-05-29| 1

sir_ter_favoritos
id|idincorporadora|status
1 |     771       | 1

sir_users
id  |name
771 | João
778 | Maria

Result that should come:

idinc|nome |totalviews|ultimav   |COUNT(f.status)
771  |João |   4      |2018-05-29|    1
778  |Maria|   1      |2018-05-29|    0

Outcome that is coming:

idinc|nome |totalviews|ultimav   |COUNT(f.status)
771  |João |   4      |2018-05-29|    2
778  |Maria|   1      |2018-05-29|    0
sql select

  • Can’t you set an example with some data to facilitate understanding? If you are duplicating the data, you may need to group, but without seeing the data it is difficult

  • opa, of course, I edited the answer, I put an example

  • Now :) apparently is doubling because of the table sir_ter_relatorios_terrenos have two records with the same iduser (771). Including this table sir_users could be in the JOIN and not in the WHERE, would be clearer the relation. I found it strange not to error in the query because in select has the field u.name as nome that is not stapled and also not part of the group by, this should generate an error in this field

  • understood, then, it should bring Count as 1, because it has 1 entry in the favorites, no?

  • I managed to find the solution, as put here?

  • Just write in the answer and post explained, you can answer the question itself

Show 1 more comment

1 answer

0


I decided by making the query using left Join with select inside another:

SELECT u.id, u.name, r.visualizacoes, f.favoritos, r.datav
FROM sir_users u
LEFT JOIN (SELECT iduser, SUM(views) visualizacoes, MAX(DATE_FORMAT(data, '%d/%m/%Y %H:%i')) datav FROM sir_ter_relatorios_terrenos GROUP BY iduser) r ON u.id = r.iduser
LEFT JOIN (SELECT idincorporadora, COUNT(status) favoritos FROM sir_ter_favoritos GROUP BY idincorporadora) f ON u.id = f.idincorporadora
GROUP BY u.id
ORDER BY r.visualizacoes DESC

  • mark the answer as accepted ;)

Browser other questions tagged sql select

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