SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  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...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i think pootle flump is giving you excellent advice on that other forum
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict evilone's Avatar
    Join Date
    Oct 2004
    Location
    Estonia
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, I made it pretty far there, can you give me some advice, how to finalize that query?
    I'd change the world but God won't give me the source code...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •