Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    14

    Unanswered: SQL Question (Merging a query?)

    Not exactly sure what to call this, or even to search for!

    Here's the problem, I have a parent and child table. I want to extract all info from the parent table, but have any child info available override the parent info.
    Code:
    PARENT_TABLE                                 Child_TABLE
    Id   Pname   PGender                         ID Parent_ID Pname Pgender
    1    Bob       Male                              1  1            Billy
    2    Milly      Female
    3    Bucky    Male                              2  3                      Female
    So my mystery SQL query would produce a juxtaposition of the tables such that entries in child_table would override those in parent_table and the results would be:

    Report:
    Name Gender
    Billy Male
    Milly Female
    Bucky Female


    Is there any way to do this in Oracle SQL?

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    select (case child_table.pname
                    when is null then parent_table.pname
                    else child_table.pname
              end) as adj_pname,
             (case ...
    from ...
    where ...
    -cf

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Or using the DECODE function (less fancy )
    Code:
    SELECT DECODE(c.pname, NULL, p.pname, c.pname) name,
           DECODE(c.pgender, NULL, p.pgender, c.pgender) gender
    FROM PARENT p, CHILD c
    WHERE p.id = c.parent_id (+)

  4. #4
    Join Date
    Mar 2004
    Posts
    14
    Oh rats! I was hoping I could avoid decode since I have a bazillion fields and I'll have to decode every field and maintaining it is going to be a ugly. I was hoping there might be a SMUSH set operator.

    Oh well, ya gotta do what you gotta do!

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Well you could create a procedure which given a list of parent/child table pairs (from a table say) could create views using info from user_tab_columns. That way your code would only interrogate the views and you could have an oracle job to run your procedure when there is a table change or at a regular intervals.

    Alan

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    If you don't like DECODE or CASE, I guess there's still NVL or COALESCE

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Like WilliamR said, use nvl, this is what it is made for.

    SELECT nvl(c.pname, p.pname) name,
    nvl(c.pgender, p.pgender) gender
    FROM PARENT p, CHILD c
    WHERE p.id = c.parent_id (+)
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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