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@xxxxxxx > On 08-Apr-2019, at 3:28 PM, Szymon Lipiński <mabewlun@xxxxxxxxx> 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@xxxxxxx> 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@xxxxxxx > > > > >
