Exists subquery - missing results

[tmccoy1]

I would greatly appreciate extra eyes to try to solve this issue. The EXISTS subquery is eliminating a row(s) of data that it should not. The first part of the query, prior to the EXISTS subquery returns the record in question, and a separate query on the table used in the EXISTS subquery confirms that a record exists that matches the subquery criteria exactly, yet when I run the whole thing, that record is not in the results. Is there something with my structure or syntax that might cause this?

SELECT chrg_2B.field1, hist_2B.field2, pmt_2B.field3, etc.

FROM billing_tx_charge_detail AS chrg_2B
INNER
JOIN billing_tx_history AS hist_2B
ON chrg_2B.FACILITY = hist_2B.FACILITY
AND chrg_2B.PATID = hist_2B.PATID
AND chrg_2B.EPISODE_NUMBER = hist_2B.EPISODE_NUMBER
AND chrg_2B.JOIN_TO_TX_HISTORY = hist_2B.JOIN_TO_TX_HISTORY

LEFT OUTER
JOIN billing_pay_adj_history as pmt_2B
ON pmt_2B.FACILITY = chrg_2B.FACILITY
AND pmt_2B.PATID =chrg_2B.PATID
AND pmt_2B.JOIN_TO_TX_HISTORY = chrg_2B.JOIN_TO_TX_HISTORY
AND pmt_2B.GUARANTOR_ID = chrg_2B.GUARANTOR_ID
AND pmt_2B.date_of_service = chrg_2B.date_of_service
AND pmt_2B.JOIN_TO_PAYMENT_HISTORY = chrg_2B.JOIN_TO_PAYMENT_HISTORY_

WHERE
chrg_2B.GUARANTOR_ID = '5001'
AND chrg_2B.v_program_of_visit_code = '33131'
AND chrg_2B.date_of_service >= '10/01/2011'
AND chrg_2B.date_of_service <= '10/31/2011'

AND
EXISTS
(SELECT *
from billing_pay_adj_history pmt_BB
where pmt_BB.PATID = pmt_2B.PATID
AND pmt_BB.date_of_service = pmt_2B.date_of_service
AND pmt_BB.GUARANTOR_ID IN ('9999','9997','8000')
AND pmt_BB.v_program_of_service_code = '331310')

 

[tmccoy1]

I found a solution. In the exists clause I had to change the reference to the initial select from the pmt_2B table to the chrg_2B table.

 

[imex]

Try:

AND
EXISTS
    (SELECT *
     from billing_pay_adj_history pmt_BB
     where pmt_2b.PATID is null OR
          (pmt_BB.PATID = pmt_2B.PATID                     AND
           pmt_BB.date_of_service = pmt_2B.date_of_service AND
           pmt_BB.GUARANTOR_ID IN ('9999','9997','8000')   AND
           pmt_BB.v_program_of_service_code = '331310') )

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[imex]

Sorry, you had not posted when I started posting.