Re: SQl help to build a result with custom aliased bool column

I am still having some bugs. I am getting duplicate in the result set.

psql (11.0, server 10.5)
Type "help" for help.

aruprakshit=# select * from features;
 id | name
----+------
  1 | f1
  2 | f2
  3 | f3
  4 | f4
(4 rows)

aruprakshit=# select * from company;
 id | name
----+------
  1 | c1
  2 | c2
(2 rows)

aruprakshit=# select * from company_features;
 id | company_id | feature_id
----+------------+------------
  1 |          1 |          1
  2 |          1 |          2
  3 |          2 |          3
  4 |          1 |          3
(4 rows)

aruprakshit=# SELECT
aruprakshit-# features.id,
aruprakshit-# features.name,
aruprakshit-# coalesce(company_features.company_id = 1, false) AS active
aruprakshit-# FROM
aruprakshit-# features
aruprakshit-# LEFT JOIN company_features ON features.id = company_features.feature_id;
 id | name | active
----+------+--------
  1 | f1   | t
  2 | f2   | t
  3 | f3   | f
  3 | f3   | t
  4 | f4   | f
(5 rows)


I should get in the results only 3, as total number of features are 3.

Thanks,

Arup Rakshit
ar@zeit.io



> On 08-Apr-2019, at 3:28 PM, Szymon Lipiński <mabewlun@gmail.com> wrote:
>
> 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@zeit.io> wrote:
> 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
>
>
>
>
>