Hi guys,
I need to do very complicated SQL query from database. I’m using MS SQL database. I added pdf with my ERD model.
I need to select different columns from many different tables.
It’s a cadastre database for storing cadastre data. Like there’s a house with apartments and each apartment has owner, sometimes more than one. I need to query the persons, their ID-s, apartment no., address, city and so on…
I’ve tried to query it like
SELECT (i.eesnimi + ' ' + i.perenimi_firma) AS nimi, ik.isikukood, ri.yldpind, ri.number, a.aadress_haldusyksus, a.aadress_tanav FROM KINNISTU_DETAIL AS kd
INNER JOIN JAGU AS j ON j.kinnistu_detail_id = kd.kinnistu_detail_id
INNER JOIN OMAND AS o ON o.jagu_id = j.jagu_id
INNER JOIN ISIK AS i ON i.omand_id = o.omand_id
INNER JOIN ISIKUKOOD AS ik ON ik.isik_id = i.isik_id
INNER JOIN KATASTRIYKSUS AS ky ON ky.jagu_id = j.jagu_id
INNER JOIN REAALOSA_INFO AS ri ON ri.katastriyksus_id = ky.katastriyksus_id
INNER JOIN AADRESS AS a ON a.katastriyksus_id = ky.katastriyksus_id
WHERE j.kehtivus = 'kehtiv'
AND kd.uus_nr = '8958201'
I need to select yldpind, number from REAALOSA_INFO, aadress_haldusyksus, aadress_tanav from AADRESS, eesnimi, perenimi_firma, synniaeg from ISIK, isikukood from ISIKUKOOD and the filters are kehtivus in JAGU and uus_nr in KINNISTU_DETAIL.
It works until INNER JOIN ISIKUKOOD, if I insert INNER JOIN KATASTRIYKSUS then it don’t return any rows.
I’m not very good in English, maybe you understand what I need to do.
Also all tables are filled, so the problem is not in the empty tables in my test database.