How to create a Does Not Contain query

[mholbert]

Im searching a sql database and trying to find where item A exists, but item B doesn't exist.

so far I have something like the following:

select c.enc_id,s.sim
into #one
from charges c
where c.sim in ('list of serveral sim codes')

that gives me a temp table with the encounter ids and the codes that are attached to a particular encounter. I can't figure out how to query "give me the enc_id's that don't have the sim 61798 attached to them"


I'm sure I'm missing something simple.

Thanks in advance for the help. MH

 

[RiverGuy]

First observation, I don't see how your code provided works. You are specifying table c in your From clause, but you are selecting from table s as well. I don't see table s in your query.

As far as the "Does not contain" portion, have you looking at NOT IN and NOT EXISTS?

 

[mholbert]

oops, that should have been

select c.enc_id,c.sim
into #one
from charges c
where c.sim in ('list of serveral sim codes')

i haven't used the NOT IN or NOT EXISTS before. I will look into those. thx

 

[RiverGuy]

It's the same concept as you have with your IN clause. NOT EXISTS can be utilized when you need to stipulate criteria which covers multiple columns.

 

[mholbert]

OK, no it seems pretty cut and dried using the NOT EXISTS, but I can't seem to get any answers.

Here is the actual query.

select distinct(c.source_id) as enc_id
into #CT
from charges c
inner join patient_encounter pe on pe.enc_id = c.source_id
inner join encounter_payer ep on ep.enc_id = pe.enc_id
inner join payer_mstr pm on pm.payer_id = ep.payer_id
where c.service_item_id in '80312','80313','80314','80316','80317','80319','80320','80321','80323','80324','80326','80327','80329','80330','80331','80333','80334','80336','80337','80339','80340','80342','80343','80344','80346','80347','80348','80350','80351','80399')
and pm.payer_name like 'medicare%'
and left(c.closing_date,4) in ('2007','2008')

select c.service_item_id as SIM,c.source_id
into #S
from charges c
inner join #CT on #CT.enc_id = c.source_id

select *
from #s
where not exists (select sim from #S where #s.sim = '61798')

The first part gets all the encounter's that have a CT. The second part gets all the codes from all those encounters. The third part should get only those encounters that don't have the code 61798, but it doesn't.

MH

 

[SQLSister]

Use a left join to the table you want to compare it to. and then add a where clause that looks to see if the id_field of that table is null.

"NOTHING is more important in a database than integrity." ESquared