Make Mysql query with 3 tables

Navigation
0

I have the following query that should bring the sum of views and last view (table sir_ter_relatorios_terrenos ), sum of favorites (table sir_ter_favoritos ) and person 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 comes to bringing in COUNT(f.status) is giving something wrong, instead of bringing 1 (which is the only record you have on that table) it is bringing 3, which is a count of items in 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

Result 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
    
asked by anonymous 29.05.2018 / 15:24

1 answer

0

I solved 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
    
29.05.2018 / 19:59
Modeling Banks Financial Institution Get variable value optionalObject []