I have the following two tables:

create table tab1(c1 number);

create table tab2(c2 number, c22 number);

insert into tab1 values(1);
insert into tab1 values(2);
insert into tab1 values(3);
insert into tab1 values(4);
insert into tab1 values(5);

insert into tab2 values(1, null);
insert into tab2 values(3, 1);
insert into tab2 values(4, null);
insert into tab2 values(4, 1);

Column c1 in table tab1 and c2 in table tab2 are the joining columns.

Note that there can be more than one record in table tab2 that matches a particular c1 value in table tab1.

I would like to see the following result when the two tables are joined:

C1 DATA_COLX
---- ----------
1 Y
2 N
3 N
4 Y
5 N

If there is at least one matching record in tab2 where c22 is null for a particular c1 value, a 'Y' is returned for column DATA_COLX.

If there is no matching record in tab2 for a particular c1 value, a 'N' is returned for column DATA_COLX.

If there are matching records in tab2 for a particular c1 value and c22 is not null, then return 'N' for column DATA_COLX.

The following query will produce the desired result:

select c1, 'Y' DATA_COLX from tab1
where exists (select 1 from tab2 where c2 = c1 and c22 is null)
union all
select c1, 'N' DATA_COLX from tab1
where not exists (select 1 from tab2
where c2 = c1)
union all
select c1, 'N' DATA_COLX from tab1
where exists (select 1 from tab2 a
where c2 = c1
and c22 is not null
and not exists (select 1 from tab2 b
where b.c2 = a.c2 and b.c22 is null))
order by c1

Can the result be achieved using a single SELECT statement?

Thank you for your help.
Philip

ops$tkyte%ORA10GR2> select tab1.c1, decode( tab2.c2, NULL, 'N', 'Y' )
  2    from tab1,
  3        (select distinct c2 from tab2 where c22 is null) tab2
  4   where tab1.c1 = tab2.c2(+)
  5   order by tab1.c1
  6  /

        C1 D
---------- -
         1 Y
         2 N
         3 N
         4 Y
         5 N