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...
Kimberly Yelton
Last Activity: Mar 15 '11, 02:01 PM
Joined: Jan 28 '11
Location:
-
When I try this query, it only gives me the record = to the 'Hello' part, not all the records with the same HHNUM.Leave a comment:
-
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.PROFI T
FROM DECTEST2 b
INNER JOIN (
SELECT DISTINCT
x.HHNUM,x.PTYPE ,x.STYPE,x.NIBO AC,x.HHNIBOAC,x .TOTBAL,x.BANK,
x.BRANCH,x.CENT ,x.[DEC],x.TIER,x.PROFI T
FROM DECTEST2 x
WHERE x.PTYPE IN ('DDA', 'MMA')) v
...Leave a comment:
-
How to select all records one criteria, but not another?
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...
No activity results to display
Leave a comment: