Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2009
    Posts
    19

    Unanswered: Very complicated SELECT query

    I 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.
    Attached Files Attached Files
    Last edited by jakko100; 01-25-10 at 16:16.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It would appear then that there are no KATASTRIYKSUSes for any JAGUs.
    Does the below work ok?
    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
    LEFT OUTER JOIN KATASTRIYKSUS AS ky ON ky.jagu_id = j.jagu_id
    LEFT OUTER JOIN REAALOSA_INFO AS ri ON ri.katastriyksus_id = ky.katastriyksus_id
    LEFT OUTER JOIN AADRESS AS a ON a.katastriyksus_id = ky.katastriyksus_id
    WHERE j.kehtivus = 'kehtiv'
    AND kd.uus_nr = '8958201'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by pootle flump View Post
    It would appear then that there are no KATASTRIYKSUSes for any JAGUs.
    If only I had a nickel for every time I've had THAT problem...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2009
    Posts
    19
    pootle flump, I haven't tested the code yet, but there's definitely rows on KATASTRIYKSUSes, because for each JAGU there multiple rows on KATASTRIYKSUS.

    If the LEFT JOIN is the same that LEFT OUTER JOIN then I had tried that and without luck.

    But I will try the code in couple of hours and let's see

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You would have needed all the left outer joins. If you only changed the KATASTRIYKSUS join to JAGU only then even with matches you might have no rows returned, since there might have been no
    REAALOSA_INFOs for your KATASTRIYKSUSes.

    Just shove them all in and see if it runs. If it does then it should be easy to spot the table(s) with no matching rows.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2009
    Posts
    19
    For every KATASTIYKSYS there's always rows in REAALOSAINFO too. In one query it fills all the tables from JAGU. JAGU->KATASTRIYKSYS->REAALOSA_INFO->AADRESS->OMAND->ISIK->ISIKUKOOD are all filled. In my testing database there's data too.

    But I'll try your suggestion and see'ya in couple of hours.

  7. #7
    Join Date
    Nov 2009
    Posts
    19
    This query doesn't work. It returns eesnimi, perenimi_firma and isikukood fine. But all others are just NULL-s.

    If I do SELECT * FROM KATASTRIYKSYS then the result is

    Code:
    katastriyksus_id	jagu_id	pindala_yhik_id	tunnus	          pindala	plaani_alusel	avatud	                       avaja_id	muudetud	muutja_id	       suletud	             sulgeja_id
    1	                               2	4	                39501:001:0073	179024.00	NULL	             2010-01-21 23:25:18.300	        JS	        NULL	        NULL	        3000-12-12 00:00:00.000	NULL
    2	                               7	4	                78403:310:0880	7862.00	0	             2010-01-21 23:25:52.073	        JS	        NULL	        NULL	        3000-12-12 00:00:00.000	NULL
    3	                              12	4	                78403:315:2080	2185.00	0	             2010-01-21 23:26:10.057 	JS	        NULL	        NULL	        3000-12-12 00:00:00.000	NULL
    4	                              18	4	                78403:315:2080	2185.00	0	             2010-01-21 23:26:15.493 	JS	        NULL	        NULL	        3000-12-12 00:00:00.000	NULL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jakko100 View Post
    It returns eesnimi, perenimi_firma and isikukood fine. But all others are just NULL-s.
    That means there are no matching rows based on the column you join on.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2009
    Posts
    19
    Yes there is

    If I query like this

    Code:
    SELECT * FROM KINNISTU_DETAIL AS kd 
    INNER JOIN JAGU AS j ON j.kinnistu_detail_id = kd.kinnistu_detail_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'
    then it returns all the data I need for the other part of the query I posted earlier...

    Those columns that I matching on are Primary Keys and Foreign Keys only. On JAGU there's primary key jagu_id and foreign key kinnistu_detail_id for KINNISTU_DETAIL, on KATASTRIYKSUS there's primary key katastriyksus_id and foreign key jagu_id for JAGU, on AADRESS there's primary key aadress_id and foreign key katastriyksus_id for KATASTRIYKSUS.

    Table JAGU is connecting those two paths and this is important point here...I think...

    In table JAGU there's like two paths. If I query one path to ISIKUKOOD then it works and if I query the other path to REAALOSA_INFO then it works too. But I don't get it, how to connect them.

    Ok, I did a little querying to database and write here the real primary keys and foreign keys that's are in tables:

    Code:
    KATASTRIYKSYS
    -------------------
    katastriyksus_id
    1
    2
    3
    4
    Code:
    JAGU
    -------------------------
    jagu_id     katastriyksus_id
    1	             1
    2	             1
    3	             1
    4	             1
    5	             1
    6	             2
    7	             2
    8	             2
    9	             2
    10	             2
    11	             3
    12	             3
    13	             3
    14	             3
    15	             3
    16	             3
    17	             4
    18	             4
    19	             4
    20	             4
    21	             4
    22	             4
    23	             4
    24	             4
    Code:
    KATASTRIYKSUS
    ---------------------------
    katastriyksus_id     jagu_id
    1	                   2
    2	                   7
    3	                   12
    4	                   18
    Code:
    OMAND
    ---------------------------
    omand_id     jagu_id
    1	           3
    2	           8
    3	           9
    4	           9
    5	          13
    6	          19
    7	          20
    8	          21
    Code:
    REAALOSA_INFO
    ------------------------------------
    reaalosa_info_id     katastriyksus_id
    1	                    2
    Code:
    AADRESS
    -----------------------------
    aadress_id     katastriyksus_id
    1	            1
    2	            2
    3	            3
    4	            4
    Code:
    ISIK
    --------------------
    isik_id     omand_id
    1	       1
    2	       2
    3	       3
    4	       4
    5	       5
    6	       6
    7	       7
    8	       8
    Code:
    ISIKUKOOD
    ------------------------
    isikukood_id     isik_id
    1	              1
    2	              2
    3	              3
    4	              4
    5	              6
    6	              7
    7	              8
    Last edited by jakko100; 01-26-10 at 17:25.

  10. #10
    Join Date
    Nov 2009
    Posts
    19
    I think you're right, sorry

    The query I first posted where's a filters in where clause then this query will result: jagu_id in KATASTRIYKSUS is 7 and in OMAND jagu_id is 9...

    Prrr...but how I get the right data then...

    Two queries and later join the results somehow??? - like kinnistu_detail_id in KINNISTU_DETAIL are same on two queries. How can I add those two queries together and compare the kinnistu_detail_id?

    If I do those three queries and can put them into one statement somehow and filter the results by the WHERE clause below, then it works maybe?

    Code:
    (SELECT o.jagu_id, i.eesnimi, i.perenimi_firma, i.synniaeg, ik.isikukood FROM ISIK AS i
    INNER JOIN OMAND AS o ON o.omand_id = i.omand_id
    INNER JOIN ISIKUKOOD AS ik ON ik.isik_id = i.isik_id) AS q1
    
    (SELECT ky.jagu_id, ri.yldpind, ri.number, a.aadress_haldusyksus, a.aadress_tanav FROM REAALOSA_INFO AS ri
    INNER JOIN KATASTRIYKSUS AS ky ON ky.katastriyksus_id = ri.katastriyksus_id
    INNER JOIN AADRESS AS a ON a.katastriyksus_id = ky.katastriyksus_id) AS q2
    
    (SELECT j.jagu_id, j.kehtivus ,kd.uus_nr FROM JAGU AS j
    INNER JOIN KINNISTU_DETAIL AS kd ON kd.kinnistu_detail_id = j.kinnistu_detail_id) AS q3
    Code:
    WHERE q3.uus_nr = '8958201' AND q3.kehtivus = 'kehtiv' AND q1.jagu_id = q3.jagu_id AND q2.jagu_id = q3.jagu_id
    Then complete query will be like this?

    Code:
    SELECT q1.eesnimi, q1.perenimi_firma FROM JAGU AS j
    INNER JOIN KINNISTU_DETAIL AS kd ON kd.kinnistu_detail_id = j.kinnistu_detail_id,
    (SELECT o.jagu_id, i.eesnimi, i.perenimi_firma, i.synniaeg, ik.isikukood FROM ISIK AS i
    INNER JOIN OMAND AS o ON o.omand_id = i.omand_id
    INNER JOIN ISIKUKOOD AS ik ON ik.isik_id = i.isik_id) AS q1,
    (SELECT ky.jagu_id, ri.yldpind, ri.number, a.aadress_haldusyksus, a.aadress_tanav FROM REAALOSA_INFO AS ri
    INNER JOIN KATASTRIYKSUS AS ky ON ky.katastriyksus_id = ri.katastriyksus_id
    INNER JOIN AADRESS AS a ON a.katastriyksus_id = ky.katastriyksus_id) AS q2
    WHERE kd.uus_nr = '8958201' AND j.kehtivus = 'kehtiv' AND q1.jagu_id=j.jagu_id AND q2.jagu_id = j.jagu_id
    But it doesn't work
    Last edited by jakko100; 01-26-10 at 18:42.

  11. #11
    Join Date
    Nov 2009
    Posts
    19
    Ok, I think I got the solution

    Code:
    SELECT q1.eesnimi, q1.perenimi_firma FROM JAGU AS j
    INNER JOIN KINNISTU_DETAIL AS kd ON kd.kinnistu_detail_id = j.kinnistu_detail_id
    INNER JOIN
    (SELECT o.jagu_id, i.eesnimi, i.perenimi_firma, i.synniaeg, ik.isikukood FROM ISIK AS i
    INNER JOIN OMAND AS o ON o.omand_id = i.omand_id
    INNER JOIN ISIKUKOOD AS ik ON ik.isik_id = i.isik_id) AS q1 ON q1.jagu_id IN (j.jagu_id)
    LEFT JOIN
    (SELECT ky.jagu_id, ri.yldpind, ri.number, a.aadress_haldusyksus, a.aadress_tanav FROM REAALOSA_INFO AS ri
    INNER JOIN KATASTRIYKSUS AS ky ON ky.katastriyksus_id = ri.katastriyksus_id
    INNER JOIN AADRESS AS a ON a.katastriyksus_id = ky.katastriyksus_id) AS q2 ON q2.jagu_id IN (j.jagu_id)
    WHERE kd.uus_nr = '8958201' AND j.kehtivus = 'kehtiv'
    Maybe you check that and let me know is this right or not.

    Sorry for spamming topic...


    Edit: Not still working...but I can feel that I'm close...

  12. #12
    Join Date
    Nov 2009
    Posts
    19
    still no luck...I've tried so many versions...

    The last query is like that...

    Code:
    SELECT q1.eesnimi, q1.perenimi_firma, q1.synniaeg, q1.isikukood, q2.aadress_haldusyksus, q2.aadress_tanav FROM JAGU AS j
    INNER JOIN KINNISTU_DETAIL AS kd ON kd.kinnistu_detail_id = j.kinnistu_detail_id
    INNER JOIN
    (SELECT o.jagu_id, i.eesnimi, i.perenimi_firma, i.synniaeg, ik.isikukood FROM ISIK AS i
    INNER JOIN OMAND AS o ON o.omand_id = i.omand_id
    INNER JOIN ISIKUKOOD AS ik ON ik.isik_id = i.isik_id) AS q1 ON q1.jagu_id = j.jagu_id
    FULL OUTER JOIN
    (SELECT ky.jagu_id, ri.yldpind, ri.number, a.aadress_haldusyksus, a.aadress_tanav FROM KATASTRIYKSUS AS ky
    FULL OUTER JOIN REAALOSA_INFO AS ri ON ri.katastriyksus_id = ky.katastriyksus_id
    INNER JOIN AADRESS AS a ON a.katastriyksus_id = ky.katastriyksus_id) AS q2 ON q2.jagu_id = j.jagu_id
    WHERE kd.uus_nr = '1554206' AND j.kehtivus = 'kehtiv'
    This query works, but don't take values from table AADRESS. Those are nulls. But there is always row in table AADRESS for table KATASTRIYKSUS. What I need to do to add this AADRESS table row to the returning result...

    Totally stuck...

    I uploaded database file and the log file to http://web.zone.ee/itk/db if someone wants to help...
    Last edited by jakko100; 01-27-10 at 16:09.

  13. #13
    Join Date
    Nov 2009
    Posts
    19
    Got it working...

Posting Permissions

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