want to find records if in another group 1

[shirley35]

I'm looking for the right syntax or even if I'm doing this right. I want to find records with certain SIM numbers and when I find that group I only want the ones that also have other certain SIM numbers.
What I have below errors out "near the keyword IF". Am I supposed to be doing a join instead or am I getting close with what I have?

SELECT c.source_id, pe.enc_nbr, c.SIM

FROM
patient_encounter pe
left outer join Charges c on (pe.person_id = c.person_id and pe.enc_id = c.source_id)

Where c.SIM in ( '99211', '99212', '99213', '99214', '99215', '99241',)
(
If exists (select SIM from charges where SIM in ('G8443', 'G8445', 'G8446')
)
Thank you.

 

[markros]

I think you're making it too complex. If this is one field from Charges table when you can have only one value in SIM, correct?

if yes,

when your query would be

where c.SIM IN ( '99211', '99212', '99213', '99214', '99215', '99241','G8443', 'G8445', 'G8446')

Am I right?

 

[Joulius]

Hi

This should work:

SELECT c.source_id, pe.enc_nbr, c.SIM
FROM patient_encounter pe
LEFT OUTER JOIN Charges c ON pe.person_id = c.person_id AND pe.enc_id = c.source_id
WHERE c.SIM IN ('99211', '99212', '99213', '99214', '99215', '99241')
	AND EXISTS (SELECT 1 FROM charges WHERE SIM IN ('G8443', 'G8445', 'G8446'))

 

[shirley35]

makros: Thank you for your reply. Sometimes there will be a 99211 where there is not a G code and I don't need those, so first I want to find my 99 codes and then from that group I want to find which also has a G code. Both the 99 numbers and the G codes are charges and there can be multiple charges.

Joulius: Thank you also. Perhaps I am doing something wrong - but it seems as if the "and exists" is getting ignored. I get the same result set whether I include that or not. I only have 4 G codes input in my test environment and I am getting 7 additional records that don't have any G codes. What you have looks right so this doesn't make sense to me.

 

[jbenson001]

you post is very confusing. You shold post some sample data and expected output.

 

[markros]

I agree with the previous poster. Please post some data and what do you want to get.

 

[RyanEK]

Joulis was close but I think he missed one condition. Can you try the following?

SELECT c.source_id, pe.enc_nbr, c.SIM
FROM patient_encounter pe
LEFT OUTER JOIN Charges c ON pe.person_id = c.person_id AND pe.enc_id = c.source_id
WHERE c.SIM IN ('99211', '99212', '99213', '99214', '99215', '99241')
AND EXISTS (SELECT 1 FROM charges c2 WHERE c.person_id = c2.person_id and c2.SIM IN ('G8443', 'G8445', 'G8446'))

Ryan

 

[shirley35]

Thank you all. Sorry for the confusion.

Ryan: By giving the charges table a different alias in the second Select, is that like joining to the charges table a second time?
This solution is much closer. Now I am only getting one record where there is not a G code.
The same patient with enc_nbr 32 has a different enc_nbr that does have a Gcode (the SIM isn't included in the first "IN" list so I didn't expect it in the result set below).
So based on what I learned from your example I just added the other condition I have on the charges join (pe.enc_id = c2.source_id) and that eliminated enc_nbr 32.

enc_nbr SIM
47 99214
19 99215
32 99214 This is the one without a Gcode
3 99211
Thank you!

 

[shirley35]

Thank you all. Sorry for the confusion.

Ryan: That solution was much closer. I only got one enc_nbr that did not belong because it didn't have a Gcode.
But from your example I learned why and I added one more condition which was c2.source_id = pe.enc_id:

SELECT c.source_id, pe.enc_nbr, c.SIM
FROM patient_encounter pe
LEFT OUTER JOIN Charges c ON pe.person_id = c.person_id AND pe.enc_id = c.source_id
WHERE c.SIM IN ('99211', '99212', '99213', '99214', '99215', '99241')
AND EXISTS (SELECT 1 FROM charges c2 WHERE c.person_id = c2.person_id and c2.source_d = pe.enc_id and c2.SIM IN ('G8443', 'G8445', 'G8446'))

and then it worked.

Is giving the charges table an alias in the second Select like joining to the charges table a second time?

Thank you -