SQL Query is creating way too many repeated rows

Question

i have an issue with a sql query and how the output is being displayed, you see, i have 3 tables and have at least one field in common, the thing is when i join 2 tables together the information i need is displayed properly, but when i join the third the output goes insane and duplicates the results way too much and i need to figure out why it is happening, down below i'll show you all the tables and relations between each other

this is how the tables are related to each other

This is how the first table (dbo_predios) is made the first three fields are the only relevant in this case

This is how the second table (dbo_permisos_obras_mayores) is made the first three fields are the only relevant in this case as well, the second two can match the first table (dbo_predios)

And here is how the third table (dbo_recepciones_obras_mayores) is made, the fourth field is the only relevant in this case, it could relate to the second table (dbo_permisos_obras_mayores) to the same name field

okay, now that is structurewise, now the query i'm executing is the following:

SELECT
dbo_predios.codigo_unico_predio,
dbo_permisos_obras_mayores.numero_permiso_edificacion,
dbo_permisos_obras_mayores.fecha_permiso_edificacion
FROM dbo_predios
INNER JOIN  dbo_permisos_obras_mayores ON dbo_predios.codigo_manzana_predio = dbo_permisos_obras_mayores.codigo_manzana_predio AND dbo_predios.codigo_lote_predio = dbo_permisos_obras_mayores.codigo_lote_predio
INNER JOIN  dbo_recepciones_obras_mayores ON dbo_permisos_obras_mayores.numero_recepcion_permiso = dbo_recepciones_obras_mayores.numero_recepcion_permiso
WHERE       dbo_permisos_obras_mayores.codigo_manzana_predio = 9402 AND dbo_permisos_obras_mayores.codigo_lote_predio = 30

And the result of executing the query in that way is this:

Later on i did some trial and error and removed the second inner join line, and the result surprised me, here is what happened:

Conclusion: in brief the third table is causing the cartesian product, why? i wish i knew why, what do you think of this particular case? i'd thank any help you could give me, thanks in advance.

Answer 1

Here's the solution - since you are saying that the numero_recepcion_permiso is blank, just add the condition to the inner join, to exclude empty ones:

SELECT
dbo_predios.codigo_unico_predio,
dbo_permisos_obras_mayores.numero_permiso_edificacion,
dbo_permisos_obras_mayores.fecha_permiso_edificacion
FROM dbo_predios
INNER JOIN  dbo_permisos_obras_mayores ON dbo_predios.codigo_manzana_predio = dbo_permisos_obras_mayores.codigo_manzana_predio AND dbo_predios.codigo_lote_predio = dbo_permisos_obras_mayores.codigo_lote_predio
INNER JOIN  dbo_recepciones_obras_mayores ON dbo_permisos_obras_mayores.numero_recepcion_permiso = dbo_recepciones_obras_mayores.numero_recepcion_permiso 
            AND dbo_recepciones_obras_mayores.numero_recepcion_permiso <>''
WHERE       dbo_permisos_obras_mayores.codigo_manzana_predio = 9402 AND dbo_permisos_obras_mayores.codigo_lote_predio = 30

With that said, should that field allowed to be blank or NULL? Perhaps you need to add a constraint to your table to prevent that scenario. Another suggestion - why did you choose NUMERIC(18,0) as the data type on the primary key for those tables? I would prefer a simple INT or BIGINT and maybe let the database generate the sequence for me.