Re: 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 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
ar@zeit.io