SQl help to build a result with custom aliased bool column - Mailing list pgsql-general

I have 2 tables Company and Feature. They are connected via a join table called CompanyFeature. I want to build a
resultset where it will have id, name and a custom boolean column. This boolean column is there to say if the feature
ispresent for the company or not. 

Company table:

    | id | name |
    |----|------|
    | 1  | c1   |
    | 2  | c2   |
    | 3  | c3   |

Feature table:

    | id | name |
    |----|------|
    | 1  | f1   |
    | 2  | f2   |
    | 3  | f3   |

Company Feature table:

    | id | feature_id | company_id |
    |----|------------|------------|
    | 1  | 2          | 1          |
    | 2  | 1          | 1          |
    | 3  | 3          | 2          |

The result should look like for company `c1`:

    | id | name | active |
    |----|------|--------|
    | 1  | f1   | t      |
    | 2  | f2   | t      |
    | 3  | f3   | f      |

I tried something like:

SELECT
    features.id,
    features.name,
    CASE WHEN company_features.company_id = 1 THEN
        TRUE
    ELSE
        FALSE
    END AS active
FROM
    features
    LEFT JOIN company_features ON company_features.feature_id = features.id

It works. But is there any better way to achieve this?


Thanks,

Arup Rakshit
ar@zeit.io