ssgtob1 ssgtob1 - 3 days ago 4
SQL Question

SQL Join two tables but only where certain conditions exist explicitly

For some reason I cannot get my head wrapped around this. I have two tables and I need to return just one field from one of them when all of the dependent rows in the joined table meet certain criteria. A simplified version of tables:

COMPONENT:

RegionAcronym

OfficeCode


OFFICE:

OfficeCode

LimitSwitch

CloseDate


What I need is to get the RegionAcronym from COMPONENT when every OfficeCode with that RegionAcronym has the LimitSwitch set to 'Y' and CloseDate of NULL or > CURRENT_DATE in OFFICE. The problem I run in to is that there are OfficeCode's in the COMPONENT table that do not have entries in the OFFICE table.

I tried this:

SELECT c.RegionAcronym FROM cmpnt c
LEFT JOIN Office o
ON o.OfficeCode = c.OfficeCode AND o.LimitSwitch = 'Y'
AND (o.CloseDate = NULL or o.CloseDate > CURRENT_DATE
GROUP BY c.RegionAcronym
HAVING COUNT(o.OfficeCode) = COUNT(c.OfficeCode)


This does not return the right acronyms. I believe it is not excluding OfficeCodes in the COMPONENT table when there are no records in OFFICE.

Thanks in advance.

Answer

If I understand correctly, then NOT EXISTS seems like a good way to express this:

SELECT c.RegionAcronym
FROM cmpnt c
WHERE NOT EXISTS (SELECT 1
                  FROM Office o
                  WHERE o.OfficeCode = c.OfficeCode AND
                        NOT (o.LimitSwitch = 'Y' AND
                             (o.CloseDate IS NULL OR o.CLoseDate > CURRENT_DATE)
                            )
                 )

In terms of performance, this does not require aggregation.

Comments