Filtering LEFT JOIN results?

SELECT * FROM authorizations a WHERE NOT EXISTS ( SELECT NULL FROM Settlements s WHERE s. Auth_id = a. Id AND s.

Status = 'ACCEPTED' ).

1 beat me to it by a hair – Eric Petroelje Aug 10 '09 at 17:30 Thanks! This worked perfectly. – MikeQ Aug 10 '09 at 18:39.

Based on your example, checking for the s. Status being null is unnecessary if you change the JOIN to be a RIGHT join. Will work with SQL Server 2005+ or Oracle 9i+: WITH unacceptedSettlements AS ( SELECT s.

Auth_id FROM SETTLEMENTS s WHERE s. Status! = 'ACCEPTED' GROUP BY s.

Auth_id) SELECT t. * FROM AUTHORIZATIONS t JOIN unacceptedSettlements us ON us. Auth_id = t.

Auth_id Any database alternative: SELECT t. * FROM AUTHORIZATIONS t JOIN (SELECT s. Auth_id FROM SETTLEMENTS s WHERE s.

Status! = 'ACCEPTED' GROUP BY s. Auth_id) us ON us.

Auth_id = t. Auth_id.

Both your solutions will not return an authorization if there is no corresponding record in settlements. In the @op's example, authorization 2 will not be returned. – Quassnoi Aug 10 '09 at 17:56 Per the OP: "How can I only fetch single authorization records that don't have a corresponding settlement record with a status of "ACCEPTED"?"

There's no requirement stated for needing AUTHORIZATION records without any SETTLEMENT records. – OMG Ponies Aug 10 '09 at 18:09 Authorization 2 doesn't have a corresponding settlement record with a status of ACCEPTED. That was the whole point of the LEFT JOIN in the original query.

– Quassnoi Aug 10 '09 at 18:21 Then why does the supplied query contain "status! = 'ACCEPTED'"? – OMG Ponies Aug 10 '09 at 18:24 Because the @op made it wrong.

– Quassnoi Aug 10 '09 at 18:29.

Try SELECT a. * FROM authorizations a LEFT OUTER JOIN (SELECT S. * from settlements s1 WHERE s1.

Status = 'ACCEPTED') ON a. Id = s. Auth_id WHERE s.

Auth_id is null This picks out all the records which are accepted and then takes the authorizations which are not inthat group.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.