Link to home
Create AccountLog in
Avatar of COBOLforever
COBOLforeverFlag for United States of America

asked on

SQL exists and not exists

Hi EE'rs,

Seems like this should work but when I check results I am getting ID's that only have one row in the ETNIC table - although it does have PRIMARY_INDICATOR = 'N' which is correct.
Structure is:
MAINTBL - keyed by ID
ETHNIC - keyed by ID and ETHNICCD

The result I need is ID's where it exists multiple times in the ETHNIC table but where none of the rows are marked with PRIMARY_INDICATOR = 'Y'

SELECT a.ID
 ,e.ETHNIC
  FROM MAINTBL a,ETHNIC e
 WHERE
   a.ID = E.ID
   AND NOT EXISTS (SELECT e2.id FROM ETHNIC E2
      WHERE a.ID = E2.ID AND E2.PRIMARY_INDICATOR = 'Y')
   AND EXISTS  (SELECT e3.id FROM ethnic e3
       GROUP BY e3.ID
       HAVING Count(e3.ID) > 1)
ASKER CERTIFIED SOLUTION
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of COBOLforever

ASKER

Too many Christmas cookies. Need more coffee. Thanks a lot. Sometimes I just don't know what I would do without you friggin' Genius'... Happy New Year and I'm sure we'll be "talking" again!