SQL help with omitting records

I have a query that is returning multiple records, these records come in pairs. Some of the info in the record pair columns is the same, ex ID and year, but other info has different values, ex column named type may be null on one and 'A' on another.

If this third co开发者_运维百科lumn type is equal to 'A' or another arbitrary string for one of the pairs, how do I go about having it exclude both records with the same id and year in the WHERE section of the SQL query? I was thinking a case statement may be used but I'm not sure.

You can use NOT EXISTS and a subquery...

SELECT id, year
FROM table t
WHERE NOT EXISTS (SELECT * FROM table t2 
                  WHERE ThirdColumn = 'A'
                  AND t2.id = t.id
                  AND t2.year = t.year)

Not so straightforward.

select t.*
from tbl t
left join (
  select t2.id, t2.year
  from tbl t2
  group by t2.id, t2.year
  having count(*) > 1 and
      count(case when type='A' then 1 end) *
      count(case when type='A' then null else 1 end) > 0
) X
  on X.id = t.id and X.year = t.year
WHERE X.id is null

• count(*) > 1 helps us consider where there is at least a pair with the same id/year
• The 2 count(case..) > 0s determine that "at least one of the types is 'A', and at least one is not
• LEFT JOIN + WHERE IS NULL is a classic technique to show only data where the left join failed, i.e. id/year pairs meeting the conditions of the subquery are taken out