SQLTeam.com | Weblogs | Forums

Select query want to see only those that are not available


#1

I have the folowing query, but i need to pass in the where condition more than 500 MRN's,

I am getting the distinct mrn's which are availble. only 450 coming as the result. but want to see those MRN's which are not in the table . those 50.

Can you please tell me how i can view those missing 50 mrn's thanks a lot for the helpful info.

Select distinct MRN from w_enc
where
MRN in (506720,
614133,
695588,
755580,
763133,
773174,
785829,
794734,
902446,
932324,
952272,
1046996,
1069518,
1079661,
1106178,
1106699,
1109447,
1124574,
1147860,
1152906,
1158501,
1165830,
1167551,
1176527,
1188625,
1189212,
1189464,
1201999,
1221795,
1224328,
1228452,
1231504,
1232014,
1233648,
1235986,
1240363,
1240935,
1253360,
1263659,
1266672,
1271883,
1274124,
1274247,
1281874,
1287881,
9025266,
60001635,
60050456,
60057677,
60060304,
60069116,
60069760,
60070834,
60072548,
60077770,
60078753,
60082776,
60085432,
60091607,
60103280,
60106029,
60112857,
60124388,
60126047,
60126875,
60136764,
60136897,
60137455,
60139254,
60139759,
60144621,
60152319,
60152468,
60154398,
60155709,
60157804,
60162904,
60163614,
60163749,
60166734,
60166835,
60176631,
60179802,
60187287,
60193442,
60194032,
60194572,
60198362,
60199348,
60199806,
60200654,
60203237,
60209494,
60211804,
60211906,
60212942,
60213895)


#2

Try putting the MRN values into a temporary table. Then use a join to get the ones you want. Something like

CREATE TABLE #MyTemp (MRN INT);
INSERT INTO #MyTemp (MRN) VALUES 
(614133),
(695588),
(755580),
(763133),
(773174),
(785829),
(794734),
(902446),
(932324),
....
;
SELECT DISTINCT MRN FROM w_enc 
INNER JOIN ON #MyTemp ON w_enc.MRN = #MyTemp.MRN;

#3

OP wants the missing mrn's, so select should be something like:

select mrn
  from #mytemp as a
 where not exists(select 1
                    from w_enc as b
                   where b.mrn=a.mrn
                 )
;