Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    10

    Exclamation Unanswered: Nested table and left outer join problem

    Hi,

    i have to write a sql request on a strange DB schema...

    i need to process a left outer join on table t1 and the content of each nested table t1.nested_table, how can i do that ?

    i try select * from t1, table(nested_table) , it return only row where the nested_table is not null...

    t1.id t1.nested_table
    -------------------------
    1 {a,b}
    2 {}
    3 {c}

    my 1st request return this

    t1.id t1.nested_table column_value
    -------------------------------------------
    1 {a,b} a
    1 {a,b} b
    3 {c} c

    and i want that

    t1.id t1.nested_table column_value
    -------------------------------------------
    1 {a,b} a
    1 {a,b} b
    2 {}
    3 {c} c

    is it possible ?

    Thanks a lot !

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Nested table and left outer join problem

    I believe the answer to be:

    select * from t1, table(t1.nested_table) (+)

    ... but I am not in a position to verify this at the moment.

    I abhor the use of nested tables in database design, and there is no necessity for it. It locks your data up into a difficult-to-access structure, and forces you to access the data in a specific way. If an application requires an object-oriented view of the data, then you can just give it exactly that - an OBJECT VIEW based on the RELATIONAL tables. Your application won't know the difference, and your reporting tools and ad hoc SQL queries can get the data they want without navigating through nested tables etc.

  3. #3
    Join Date
    Dec 2002
    Posts
    10

    Many thanks

    That's work !!! why i didn't find it !?!

    Thank's a lot,

    Julien

  4. #4
    Join Date
    May 2012
    Posts
    1
    Quote Originally Posted by andrewst View Post
    I believe the answer to be:

    select * from t1, table(t1.nested_table) (+)

    ... but I am not in a position to verify this at the moment.

    I abhor the use of nested tables in database design, and there is no necessity for it. It locks your data up into a difficult-to-access structure, and forces you to access the data in a specific way. If an application requires an object-oriented view of the data, then you can just give it exactly that - an OBJECT VIEW based on the RELATIONAL tables. Your application won't know the difference, and your reporting tools and ad hoc SQL queries can get the data they want without navigating through nested tables etc.
    I have had to develop a couple of databases using Access 2003. Both are heavily dependent on nested tables. These are used to describe products (kits) and the assemblies, sub-assemblies, sub-sub-assemblies, etc that make up the item; and attached parts (themselves kits (see above)).

    The above data is then used to describe actual items that exist, and for each of the assemblies, and sub-assemblies, attached parts etc, describe where the item is to be found in a particular case.

    I would be the first to admit that this is:
    • complex
    • costly in terms of computer resources
    • difficult to manage
      • one can't determine the nest level w/o expanding the dataset
      • one can't determine the maximum number of nested levels.


    However, I haven't been able to determine/discover a better solution, so I would really appreciate it if you would be able to point me in the right direction.

    Although I am specifically interested in your solution from the point of educating myself, the production of the current two databases has the following constraints.

    Any solution would need to be based on MS Access 2003, and VBA source code only. No non-standard .dll's or any other software as I would have to examine and 'verisign' any code to comply with my employers security policy.

    Thanks in advance for your time.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sorry, but this is an Oracle forum: I know nothing of Access 2003 and so cannot help.

Posting Permissions

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