Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    9

    Unanswered: Trap of 3 tables(Chasm Trap)

    Hi,

    I have a situation where I need to access data from 3 tables A,B,C
    where the cardinality relationship between the tables and the joins are as below.

    A ----> B
    ( one to many :For every record in A there will be many records in B)

    A-----> C
    ( one to many :For every record in A there will be many records in C)

    There is no link between B & C.

    I need to write a query that would fetch me rows from the tables A,B,C.
    The problem I faced was that the number records multiplied based on the number of matching rows between the tables.

    I tried a work around for this using Union of SQLs but,since I wanted a view out of the result set, I could not create a view out of a union of SQLs.

    Can someone suggest an alternative to this?

    Thanks,
    K Rajkumar

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Not sure the DB2 platform you are on, or what release level, but in DB2 V8.1 you can use UNION ALL (but not UNION) in a view. The difference is that UNION eliminates duplicate rows in the result set.

    There is one other restriction for using UNION ALL in a view in V8.1:

    the base tables in the operands of a UNION ALL must not be the same
    table and each operand must be deletable

    For other platforms or release levels, check the SQL Reference manual.

    It might be possible to code the SQL without a UNION ALL, but you would have to provide more detailed information about the tables and what results you are trying to achieve.

Posting Permissions

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