From: | Szymon Lipiński <mabewlun(at)gmail(dot)com> |
---|---|
To: | Arup Rakshit <ar(at)zeit(dot)io> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: SQl help to build a result with custom aliased bool column |
Date: | 2019-04-08 09:58:30 |
Message-ID: | CAFjNrYtsiGU4UXys0_Mu4-X+KOWqado9CrriwLRswsTexuN8fw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hey,
you could just use
SELECT
features.id,
features.name,
company_features.company_id = 1 as active
regards,
Szymon
On Mon, 8 Apr 2019 at 09:55, Arup Rakshit <ar(at)zeit(dot)io> wrote:
> 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(at)zeit(dot)io
>
>
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Arup Rakshit | 2019-04-08 10:02:36 | Re: SQl help to build a result with custom aliased bool column |
Previous Message | Arup Rakshit | 2019-04-08 09:39:31 | Re: SQl help to build a result with custom aliased bool column |