[turk99]

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.

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?

[turk99]

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.

[chuck_forbes]

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

[Littlefoot]

Alternatively, you could use NOT IN instead of NOT EXISTS:

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)
);

[chuck_forbes]

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