SQl help to build a result with custom aliased bool column

[Arup Rakshit]

I have 2 tables Company and Feature. They are connected via a join table called 
CompanyFeature. I want to build a result set where it will have id, name and a 
custom boolean column. This boolean column is there to say if the feature is 
present 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
a...@zeit.io