SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Threaded View

  1. #1
    SitePoint Addict evilone's Avatar
    Join Date
    Oct 2004
    Location
    Estonia
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

    Code:
    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.
    Attached Files Attached Files
    I'd change the world but God won't give me the source code...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •