Filtering LEFT JOIN results

Question

I have two tables: authorizations and settlements. 'Settlements' contains a forign key reference to authorizations.

A settlement can also have a status (ERROR, ACCEPTED, etc).

Given this data:

Authorizations           Settlements
id                id   |   auth_id   | status
-----             ---------------------------
1                  1         1          ERROR
2                  2         1          ACCEPTED

I'm trying to write a SQL query to find all authorizations that don't have an ACCEPTED settlement record. I've tried a LEFT OUTER JOIN, but it returns too many rows. For example:

SELECT * FROM authorizations a
LEFT OUTER JOIN settlements s ON a.id = s.auth_id
WHERE s.status is null OR s.status != 'ACCEPTED'

The problem with this is that it will still return an authorization record if it has more than one settlement record, and one of those is ACCEPTED. Or, if there is more than one ERROR record, the authorization will be returned twice.

How can I only fetch single authorization records that don't have a corresponding settlement record with a status of "ACCEPTED"? Is it possible with straight SQL, or will I have to filter the results in my code?

Answer 1

SELECT  *
FROM    authorizations a
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    Settlements s
        WHERE   s.auth_id = a.id
                AND s.status = 'ACCEPTED'
        )

Answer 2

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

Answer 3

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.