Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Question Unanswered: Help on returning multiple rows for a parent

    Newbie Question.
    Any assistance you can provide would be greatly appreciated.

    For a given row in tableA, I would like to return all rows from tableB, that have the same value in a tableA key that links the two tables together.

    I am producing a report, and I would like the format to look similiar to:

    tableARow1col1 tableARow1col2 tableARow1col3
    ------------------------------------------------------------
    Row1 from tableB where tableBCol1 equals tableARow1col1
    ...
    RowN from tableB where tableBCol1 equals tableARow1col1
    <space>
    tableARow2col1 tableARow2col2 tableARow2col3
    ------------------------------------------------------------
    Row1 from tableB where tableBCol1 equals tableARow2col1
    ...
    RowN from tableB where tableBCol1 equals tableARow2col1

    and on and on ...

    I'm having difficulty in retrieving the multiple lines from tableB and placing them in the report output (SELECT stmt.).

    Thanks in advance for any ideas, suggestions, or help you can provide.
    Jim

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    Are you saying you want the columns listed as rows?

    Please post your current code, your current output, and then
    convert the output to how you would like it presented.

    I assume you already have:

    select b.*
    from
    tablea a
    tableb b
    where
    a.column = b.column;

    For your report you can BREAK on specific criteria in sqlplus to
    have that line-break you want.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Posts
    6
    The_Duck, thanks for responding.

    Sorry for the confusion. No I am not trying to list columns as rows.
    And it would be difficult to list the current code and output, so I'll try to explain.

    Keep in mind that I'm trying to produce a report. In general here's the format:

    Heading
    Text: Col1 from TableA Row1 Text: Col2 from TableA Row1
    Text: Col3 from TableA Row1 Text: Col4 from TableA Row1
    --------------------------------------------------------------------------
    Text: Col1 from TableB RowX (where field of TableB matches key of TableA Row1)
    Text: Col1 from TableB RowY (where keys match...)
    Text: Col1 from TableB RowZ (where keys match...)

    Text: Col1 from TableA Row2 Text: Col2 from TableA Row2
    Text: Col3 from TableA Row2 Text: Col4 from TableA Row2
    --------------------------------------------------------------------------
    Text: Col1 from TableB RowM (where field of TableB matches key of TableA Row2)
    Text: Col1 from TableB RowN (where keys match...)
    Text: Col1 from TableB RowO (where keys match...)

    and all the way through TableA, listing all matching children (from TableB) under each TableA record.

    Essentially, this is a many-to-one relationship and I want to list the child records directly underneath the parent row.

    Hopefully this helps explain, so you can offer suggestions.

    Thanks in advance.
    Jim

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    Not sure which version of Oracle you are on...


    Looks like you want a hierarchical query using START WITH/CONNECT BY.

    Something in the form like:

    Select a.col1, a.col2, a.col3
    from your_table
    start with parent_some_id is null
    connect by parent_some_id = prior some_id


    This starts at the top level, and traverses down the tree. Keep in mind that if you are not on Oracle 9i or higher, you will get an error if you try to join tables.

    If you are on 9i or higher, make a view selecting the columns you want, and then use the START WITH/CONNECT BY query on the view.

    8i or lower you have to do a workaround with an inline view.

    I know thats kind of a simple explanation, but hopefully you can do some research on Metalink or OTN and find some good examples

  5. #5
    Join Date
    Jan 2004
    Posts
    6
    ss659 Thanks.

    But I still need a few suggestions.
    Oracle version is 8.1.7. I create a view with the fields of interest, one from TableA, and two from TableB, but the hierarchical query fails with ....ORA-01437: cannot have join with CONNECT BY.
    I must be missing how to link the two tables together to list all children (applicable TableB records) under the parent (TableA record).
    TableA has an ID column of the 'parent', and TableB has any number of records for that same ID, and includes it as one of its fields.

    The difficulty I'm having is returning All 'children' in the higher level select to product the report.

    Again, thanks for the suggestions. Any further suggestions (i.e. path to follow) would be greatly appreciated.

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    Ok my suggestion would be to create the query you want listing all of the parent records, all of the child records, and any other detail records.

    Dont worry about the hierarchical part yet. Once you get all of the data that you would need from the join, create a temporary table, and insert the data as a subquery:

    EX:
    Code:
    insert into temp_table(col1, col2, col3)
    select a.col1, a.col2, b.col1
    from a, b
    where a.id = b.id
    Once you have all that data into one table, do your hierarchical query off that:

    Code:
    SELECT col1, col2
    from temp_table
    start with parent_id is null
    connect by parent_id = prior id
    Hope this gives you a good start - probably the biggest pain in the ass not to allow joins on these types of queries - Im glad they fixed it in later versions

Posting Permissions

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