How to select all records one criteria, but not another?

[Kimberly Yelton]

I am trying to create a query where when one record within a household meets my criteria, I want all records to come back in the household. But I also need to exclude certain ptypes.

For example, I want all households that have a 'NOW' ptype, but does not include a 'CD' ptype. Using the sample data below, only one of these household would qualify, and I want all those records in my answer.

HHNUM PTYPE NIBOAC
200606892 CD 0
200606892 MMA 11913
200606892 NOW 76541
200606918 CRD 0
200606918 HEQ 10893
200606918 ODP 0
200606918 NOW 42884

Thanks.

[ck9663]

What do you have so far?

~~ CK

[Kimberly Yelton]

So far I have this:

SELECT
b.HHNUM,COUNT(*) as NUMACCTS,SUM(b.NIBOAC) as TNIBOAC,
SUM(b.TOTBAL) as TOTBAL,b.BANK,b.CENT,b.[DEC],b.TIER,b.PROFIT
FROM DECTEST2 b
INNER JOIN (
SELECT DISTINCT
x.HHNUM,x.PTYPE,x.STYPE,x.NIBOAC,x.HHNIBOAC,x.TOTB AL,x.BANK,
x.BRANCH,x.CENT,x.[DEC],x.TIER,x.PROFIT
FROM DECTEST2 x
WHERE x.PTYPE IN ('DDA', 'MMA')) v
ON b.HHNUM = v.HHNUM
GROUP BY b.HHNUM,b.BANK,b.CENT,b.[DEC],b.TIER,b.PROFIT

When I try to add in the part about not pulling households with other ptypes, I get stuck.

[Rabbit]

SELECT *
FROM Table1
WHERE
     UniqueID IN
     (
     SELECT DISTINCT UniqueID
     FROM Table1
     WHERE SomeField = 'Hello'
     )
AND 
     UniqueID NOT IN
     (
     SELECT DISTINCT UniqueID
     FROM Table1
     WHERE SomeField = 'World'
     )

[Kimberly Yelton]

When I try this query, it only gives me the record = to the 'Hello' part, not all the records with the same HHNUM.

[Rabbit]

Can you post your modified SQL?

[Kimberly Yelton]

SELECT *
FROM DECTEST
WHERE
PTYPE IN
(
SELECT DISTINCT PTYPE
FROM DECTEST
WHERE PTYPE = 'NOW'
)
AND
PTYPE NOT IN
(
SELECT DISTINCT PTYPE
FROM DECTEST
WHERE PTYPE = 'CD '
)

This is what I get:

HHNUM PTYPE NIBOAC
200606892 NOW 76541
200606918 NOW 42884

What I want is this:

HHNUM PTYPE NIBOAC
200606918 CRD 0
200606918 HEQ 10893
200606918 ODP 0
200606918 NOW 42884

[Rabbit]

You're not supposed to use PTYPE, you need to use an id field. Notice how I used two different fields in my example

SELECT * 
FROM DECTEST 
WHERE 
  HHNUM IN 
  ( 
  SELECT DISTINCT HHNUM 
  FROM DECTEST 
  WHERE PTYPE = 'NOW' 
  ) 
AND 
  HHNUM NOT IN 
  ( 
  SELECT DISTINCT HHNUM 
  FROM DECTEST 
  WHERE PTYPE = 'CD ' 
  )