Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    83

    Unanswered: Use a subquery instead of a Union?

    I have two tables, one has security applied to it and the other does not. I need to pull information from both. There could be redundant data between the tables.

    Secure : Log in and see only job data for your department
    Unsecure : Some of the same columns as Secured, but you can see outside your department.

    What I need to do is combine the unique results. However, because "unsecure" does not have all the columns that "secure" does, I have to put in placeholders for the union to work. Because of the placeholders, the union thinks every result is unique.

    Code:
    SELECT  effective_dt, action,compensation
    FROM secure
    WHERE employe_id=#employe_id#
    
    UNION
    
    SELECT effective_dt,action,TO_NUMBER('')
    FROM unsecure
    WHERE employe_id=#employe_id#
    Results:

    1/1/2008 HIRE 52,000
    1/1/2008 HIRE
    8/5/2008 PAYI
    9/9/2008 TERM 56,000
    9/9/2008 TERM

    What I want to display is the 1/1 and 9/9 rows that show compensation and the 8/5 row.

    Is there some kind of subquery I could do?
    Last edited by turk99; 07-07-08 at 14:30.

  2. #2
    Join Date
    Jan 2004
    Posts
    83
    To put it another way, what I want to do is query the secure table, and if there are additional rows not in secure that are located in unsecure, I want to append results.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    select f1, f2, f3
    from   table1
    
    union all
    
    select f1, f2, f3
    from   table2 t2
    where  not exists (select 1 
                       from   table1 t1
                       where  t1.f1 = t2.f1 and
                              t1.f2 = t2.f2)
    --=cf

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Alternatively, you could use NOT IN instead of NOT EXISTS:
    Code:
    SELECT effective_dt, action, compensation FROM secure
    UNION
    (SELECT effective_dt, action, to_number('') FROM unsecure
     WHERE (effective_dt, action) NOT IN (SELECT effective_dt, action FROM secure)
    );

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Note that you might be able to exchange UNION for UNION ALL when you switch over to using a subquery. If that's possible, it will yield better performance. --=cf

Posting Permissions

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