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(at)zeit(dot)io

> On 08-Apr-2019, at 3:28 PM, Szymon Lipiński <mabewlun(at)gmail(dot)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(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
>
>
>
>
>