Query SQl with inner join to filter data based on the user that enters the system

Question

Query SQl with inner join to filter data based on the user that enters the system

Navigation

#1 by (1 votes)

1

Teng a Database with the following scheme:

The user belongs to an establishment, the establishment belongs to a municipality, the department has many municipalities and the municipality has many cantons, so I want an SQL query that returns all the cantons of all the municipalities associated with the department where the establishment.

I've tried with:

select ctn.* from ctl_canton ctn left join ctl_municipio muni on ctn.id_municipio=muni.id left join 
       ctl_establecimiento est on muni.id=est.id_municipio
       where est.id=10;

but only brings me the cantons of the municipality where the establishment is and I also want the cantons of their sister municipalities

sql postgresql

asked by Igmer Rodriguez 26.02.2018 в 15:55

source

1 answer

Error saving data in access: "can not write indexes of this type due to unsupported collating sort order" Doubt about foreach with mysql data

score 1 · Accepted Answer

As I always say, the problem in these queries, is not to do it by pieces. Try to do everything in a tiron without even analyzing what you want.

Let's see ...

We want to get the department of the municipality first. So let's start by asking the query that gets that:

SELECT 
    d.id 
FROM 
    ctl_establecimiento e LEFT JOIN ctl_municipio m ON e.id_municipo = m.id
    LEFT JOIN ctl_departamento d ON m.id_departamento = d.id
WHERE
    e.id=10

This will bring, the department for that establishment. Now we just need to use that query, as a join condition to bring the cantons. Then if we call all that query J, we could do the following:

SELECT 
    c.*
FROM 
    (TODA LA CONSULTA ANTERIOR) J LEFT JOIN ctl_municipio m ON J.id = m.id_departamento 
    LEFT JOIN ctl_canton c ON m.id = c.id_municipio

and in that way, you would have what you want.