I think you're right. That STATUS column throws a wrinkle in it.
create table vkparent (
k1 char(1),
k2 char(1),
k3 char(1),
k4 char(1),
primary key(k1, k2, k3, k4)
create table vkchild (
k1 char(1),
k2 char(1),
k3 char(1),
k4 char(1),
status char(1),
k5 char(1),
d1 char(1),
primary key(k1, k2, k3, k4, k5)

insert into vkparent values('A', 'A', 'A', 'A');
insert into vkparent values('B', 'B', 'B', 'B');
insert into vkparent values('C', 'C', 'C', 'C');
insert into vkparent values('D', 'D', 'D', 'D');
insert into vkchild values('A', 'A', 'A', 'A', 'A', '1', 'X');
insert into vkchild values('B', 'B', 'B', 'B', 'I', '1', 'X');
insert into vkchild values('D', 'D', 'D', 'D', 'I', '1', 'X');
insert into vkchild values('D', 'D', 'D', 'D', 'A', '2', 'Y');

select p.k1, p.k2, p.k3, p.k4
from vkparent p
where (p.k1, p.k2, p.k3, p.k4) not in (
select c.k1, c.k2, c.k3, c.k4 from vkchild c
where c.status = 'A')
This line won't get what you want because 'D' has both an active and an inactive row.
select p.k1, p.k2, p.k3, p.k4, c.status
from vkparent p left outer join vkchild c on
p.k1=c.k1 and p.k2=c.k2 and p.k3=c.k3 and p.k4=c.k4
where c.status is null or c.status<>'A'

Rob Berendt

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.