SQL help

Evan Cutler
Evan Cutler used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Try this:

select col1 from
(
select
      col1,
      sum(case when txt = 'ALL' then 1 end)  has_all,
      nvl(sum(case when txt != 'ALL' then 1 end),0) has_others
from tab1
group by col1
)
where has_all=1 and has_others < 3
/
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 CutlerVolunteer Chief Information Officer

Author

Commented:
BRILLIANT!!!
Worked like a charm.

Thanks much.
again...
have a good one.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Glad to help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial