Why Is this SImple Query Failing?

[lrdave36]

hey guys,

I am creating a query that will simply list everyone who does not have a stat_cd of 3.

I discovered a problem when I found one account that did have a stat_cd of 3 even though I specified in my query to ignore all 3's.
The problem has something to do with the fact that they have two records in the table and one of the records has 3. Why doesn't SQL evaluate all records, or does it just look at the first one?

Here is the table:

SSN     Enroll Date     Stat Code


2345     04-03-07           4
2345     04-03-08           3

My query is

select distinct A.mbr_ssn_nbr
from dsnp.pr01_t_mbr_sys A
where A.mbr_stat_cd not in ('3')

So how do I keep this sample SSN from being including in my query results? I tried a self table join, but that didn't seem to work.

 

[ousoonerjoe]

select distinct A.mbr_ssn_nbr
from dsnp.pr01_t_mbr_sys A
where A.mbr_stat_cd <> '3';

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[Qik3Coder]

Declare @tbl as Table(
SSN varchar(4),
EnrollDate datetime,
StatCode tinyint)

INSERT INTO @tbl
SELECT '1111', '20070403', 1 UNION ALL
SELECT '2222', '20070403', 2 UNION ALL
SELECT '3333', '20070403', 3 UNION ALL
SELECT '4444', '20070403', 4 UNION ALL

SELECT '4343', '20070403', 4 UNION ALL
SELECT '4343', '20070403', 3 UNION ALL
SELECT '2323', '20070403', 2 UNION ALL
SELECT '2323', '20070403', 3 UNION ALL
SELECT '2424', '20070403', 2 UNION ALL
SELECT '2424', '20070403', 4

[green]--EVERYTHING[/green]
SELECT DISTINCT SSN FROM @tbl

[green]--NO 3 Records[/green]
SELECT DISTINCT SSN FROM @tbl WHERE StatCode <> '3'

[green]--No SSN's with a 3 StatCode[/green]
SELECT DISTINCT SSN 
FROM @tbl 
WHERE SSN not in
	(SELECT SSN FROM @tbl WHERE StatCode = '3')

HTH,
Lodlaiden

A lack of experience doesn't prevent you from doing a good job.

 

[lrdave36]

Kinda puts me on the right track, but I can't use a temp table. I'm dealing with thousands of records here. I tried this:

select distinct A.mbr_ssn_nbr
from dsnp.pr01_t_mbr_sys A



and a.mbr_ssn_nbr not in

(select mbr_ssn_nbr from dsnp.pr01_t_mbr_sys b
where a.mbr_ssn_nbr = b.mbr_ssn_nbr
and b.mbr_stat_cd <> '3' )

Interesting, it seems to have eliminated people with two records, but everyone else is 3 status.

 

[Qik3Coder]

You missed the point.
The temp table was for setup, so I could test.

You need a white list of all the records with a 3 row.
Then you can say I explicitly don't want those rows.

SELECT DISTINCT A.mbr_ssn_nbr
FROM dsnp.pr01_t_mbr_sys A 
...
and a.mbr_ssn_nbr not in(
	select mbr_ssn_nbr 
	from dsnp.pr01_t_mbr_sys b
	where b.mbr_stat_cd <> '3' )

Lod

A lack of experience doesn't prevent you from doing a good job.

 

[ousoonerjoe]

ok... this should get you a little closer. Qik3Coder was right about segregating out the SSN's with a "3" record. Here is the same with a sub-query.

SELECT DISTINCT A.mbr_ssn_nbr
FROM dsnp.pr01_t_mbr_sys A
WHERE A.mbr_ssn_nbr NOT IN (SELECT DISTINCT b.mbr_ssn_nbr
							FROM dsnp.pr01_t_mbr_sys b
							WHERE b.mbr_stat_cd = '3');

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[lrdave36]

Thanks guys, it's working now!