Very complicated SELECT query

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.

i think pootle flump is giving you excellent advice on that other forum

r937, I made it pretty far there, can you give me some advice, how to finalize that query?

all i can suggest is that you examine your own data to see which inner joins aren’t returning any rows, and change them to outer joins as appropriate