Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2011
    Posts
    11

    Unanswered: Fetch data from mulitple table which has foreign key relationship

    Here is the Scenario :
    I have 4 tables table1(COL1,COL2,cCOL3,COL4),table2(COL1,COL2,COL3 ,COL4,COL5),table3(COL1,COL2,COL3,COL4,COL5),table 4(COL1,COL2,COL3,COL4,COL5,COL6,COL7)
    Table1
    COL1,COL2 is the Primary key in Table 1
    Table2
    COL3,COL4 are the primary key in Table 2 and COL1,COL2 is the foreign key which references table 1
    Table3
    COL5 is the primary key of Table3 and COL1,COL2 are the foreign key references Table1
    and COL3,COL4 are the foreign key references Table 2
    Table4
    COL6 is the Primary key in Table4 and COL1,COL2 are the foreign key references Table1
    and COL3,COL4 are the foreign key references Table 2 and COL5 is the FK ref Table3

    QUERY : i need to fetch the data from Table4 (COL7) where COL1,COL2 of table 4 must be equal to COL1,COL2 of table 1
    COL3,COL4 of table 4 must be equal to COL3,COL4 of table 2
    COL5 of table 4 must be equal to COL5 of table 3


    SELECT T4.COL7
    FROM Database.{SCHEMANAME}.{TABLE4}
    AS T4 WHERE
    (
    SELECT T3.COL5
    FROM Database.{SCHEMANAME}.{TABLE3}
    AS T3 WHERE T3.COL5 = T4.COL5
    )
    AND
    (
    SELECT T2.COL3,T2.COL4
    FROM Database.{SCHEMANAME}.{TABL2}
    AS T2
    WHERE T2.COL3 = T4.COL3
    AND T2.COL4 = T4.COL4
    )
    AND
    (
    SELECT T1.COL1,T1.COL2
    FROM Database.{SCHEMANAME}.{TABLE1}
    AS T1
    WHERE T1.COL1 = T4.COL1
    AND T1.COL2 = T4.COL2
    );

    Could u guys please help me to rephrase the Query ?

  2. #2
    Join Date
    Jul 2011
    Posts
    11
    Please help me !!!

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You have used sub-queries.

    Do you know about Joins? That's what you should be using

    Please refer to SQL manuals/tutorials.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jul 2011
    Posts
    11
    Thanks for the reply !!! Yeah i know
    But im working in IBM MB where it will not support for joins
    thatz why im going for subqueries

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I don't know what IBM MB is . Message Broker ? BTW, you didn't specify about the MB restriction in your original post. Are there any other 'secrets' we need to know ;-)

    If joins are not supported, you should use views as an alternative.

    HTH

    Sathyaram
    Last edited by sathyaram_s; 07-28-11 at 07:29. Reason: typo
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    i am a little confused. if all these tables are FK restricted, then if the FK exists in a chlild table, it must exist in the parent table.

    so select from table 4????????
    Dick Brenholtz, Ami in Deutschland

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I agree with Dick.

    If description of DBforumnewbie about foreign key were right,
    all rows in Table5 should satisfy the requirements of the QUERY.
    So, simple "SELECT col7 FROM table4" must be an answer for the QUERY.
    QUERY : i need to fetch the data from Table4 (COL7) where COL1,COL2 of table 4 must be equal to COL1,COL2 of table 1
    COL3,COL4 of table 4 must be equal to COL3,COL4 of table 2
    COL5 of table 4 must be equal to COL5 of table 3

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    What about
    Code:
    SELECT COL7
    FROM Database.{SCHEMANAME}.{TABLE4} t4
    WHERE EXISTS
    (
     SELECT 1 FROM Database.{SCHEMANAME}.{TABLE3}
     WHERE COL5 = t4.COL5
    )
    AND EXISTS
    (
     SELECT 1 FROM Database.{SCHEMANAME}.{TABL2}
     WHERE COL3 = t4.COL3 AND COL4 = t4.COL4
    )
    AND EXISTS
    (
     SELECT 1 FROM Database.{SCHEMANAME}.{TABLE1}
     WHERE COL1 = t4.COL1 AND COL2 = t4.COL2
    )
    (Note the added "exists" entries.)
    If all foreign keys are enforced, it would even suffice to verify that the columns 1 to 6 of TABLE4 are not NULL:
    Code:
    SELECT COL7 FROM Database.{SCHEMANAME}.{TABLE4}
    WHERE COL1 IS NOT NULL
    AND COL2 IS NOT NULL
    AND COL3 IS NOT NULL
    AND COL4 IS NOT NULL
    AND COL5 IS NOT NULL
    AND COL6 IS NOT NULL
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Jul 2011
    Posts
    11
    Thank u Peter !!!!
    it actually helped me to solve my purpose !!!

Tags for this Thread

Posting Permissions

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