Show data with result in zero mysql

Question

Show data with result in zero mysql

Navigation

#1 by (2 votes)

2

I have a database that makes queries to know the amounts of detailed information of each table, which are sorted through a "country table". When you perform the query, only the information that contains data appears, for example:

in the country table there are 4 data

and 10 data in the following tables

< a href="https://i.stack.imgur.com/6J9Zf.png">

when queries are made to the organization table

SELECT 
pais.codigo_pais,
pais.descrip_pais,
Count(organizaciones.codigo_org) AS CUENTA,

FROM
organizaciones
Inner Join pais ON pais.codigo_pais = organizaciones.codigo_pais
GROUP BY
organizaciones.codigo_pais

results in me

and when I consult the other two meeting tables

SELECT 
pais.codigo_pais,
pais.descrip_pais,
Count(reuniones.codigo_reu) AS CUENTA
FROM
reuniones
Inner Join pais ON reuniones.codigo_pais = pais.codigo_pais

GROUP BY
reuniones.codigo_pais

and commitments

SELECT 
pais.codigo_pais,
pais.descrip_pais,
Count(compromisos.codigo_reu) AS CUENTA
FROM
compromisos
Inner Join pais ON compromisos.codigo_pais = pais.codigo_pais

GROUP BY
compromisos.codigo_pais

this result appears

What I need is for the query to show me the country that registers the value "0" in the tables account2 and account3. In this case, it would include the Germany registry, as in the example below:

mysql sql

asked by Ariel Ayala 08.11.2017 в 14:46

source

1 answer

TableView and setOnCloseListener JavaFX Bat renames files from different directory

score 2 · Answer 1

If you want all countries to always appear, then you must first use the pais table and make LEFT JOIN with the other table:

SELECT  p.codigo_pais,
        p.descrip_pais,
        Count(r.codigo_reu) AS CUENTA
FROM pais p
LEFT JOIN reuniones r
    ON r.codigo_pais = p.codigo_pais
GROUP BY p.codigo_pais,
         p.descrip_pais
;

Other recommendations: Always try to use alias tables, instead of using the complete table as a prefix, this improves the understanding of the code. Also, try to get used to using code that is more standard in SQL, for example when using aggregation functions. MySQL is the only relational database engine that allows you to use an aggregation function in conjunction with other columns in SELECT , and not put those columns in GROUP BY (this gives a result with an arbitrary behavior and many unexpected times)