SQL select based on column value

We have a DB table that contains company job codes like so:

ID       Code       Company
1        EW10       ***
2        EW10       DEU
3        EW10       DEC
4        EW20       ***
5        EW30       DEU
6        EW40       DEC

The ‘***’ in company represents an enterprise level job code which can be overwritten at the employer level (DEU, DEC).

I need a select statement that returns rows with the following conditions:

• If there are no company specific Codes (e.g., EW20) then return the enterprise level row (e.g., row 4)

• If there are company specific rows, return all company specific rows

The result set I need would be the following:

ID       Code       Company
2        EW10       DEU
3        EW10       DEC
4        EW20       ***
5        EW30       DEU
6        EW40       DEC

Answer

You can can do this using an OR for the two scenarios:

SELECT *
FROM   YourTable yt1
WHERE  Company <> '***'
   OR  NOT EXISTS (
          SELECT 1 FROM YourTable yt2
          WHERE yt2.Code = yt1.Code AND yt2.Company <> '***'
       )