asked on 5/23/2012

SQL help

Greetings, I have a table:
TABLE A. with two columns:
PK dec, txt varchar2

The table has the following type of data
1 ALL
1 A
1 B
1 C
2 B
3 C
3 A
4 A
5 ALL
6 B
7 A
8 ALL
8 A
8 B
8 C

If you notice, the records can have A, B, C, or ALL with no constraints. If ALL exists, they should have A, B, C in the table as well (SEE PK's 1 and 8). Notice how PK 5 has ALL, but the A, B, nor C is in there. This happens in sporatic places throughout my table of over 100K records.

I need to write a query that finds the "PK 5's", or in other words the "ALL's" with no families. How do I locate them using SQL statement.

Thank you.

Databases Oracle Database SQL

Last Comment

slightwv (䄆 Netminder)

5/23/2012

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

5/23/2012

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

paquicuba

5/23/2012

select id
from
(
select
id, max(decode(txt,'ALL',txt)) over(partition by id) txt
from
(
select 1 id, 'ALL' txt from dual union all
select 1 id, 'A' txt from dual union all
select 1 id, 'B' txt from dual union all
select 1 id, 'C' txt from dual union all
select 2 id, 'B' txt from dual union all
select 3 id, 'C' txt from dual union all
select 3 id, 'A' txt from dual union all
select 4 id, 'A' txt from dual union all
select 5 id, 'ALL' txt from dual union all
select 6 id, 'B' txt from dual union all
select 7 id, 'A' txt from dual union all
select 8 id, 'ALL' txt from dual union all
select 8 id, 'A' txt from dual union all
select 8 id, 'B' txt from dual union all
select 8 id, 'C' txt from dual
) table_a
)
where txt = 'ALL'
group by id
having count(*) < 4

Evan Cutler

5/23/2012

ASKER

BRILLIANT!!!
Worked like a charm.

Thanks much.
again...
have a good one.

slightwv (䄆 Netminder)

5/23/2012

Glad to help!