From: | Arup Rakshit <ar(at)zeit(dot)io> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | SQl help to build a result with custom aliased bool column |
Date: | 2019-04-08 07:54:47 |
Message-ID: | 6AC24472-2D13-49DD-A29E-9FB41321CCEF@zeit.io |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)zeit(dot)io
From | Date | Subject | |
---|---|---|---|
Next Message | Arup Rakshit | 2019-04-08 09:39:31 | Re: SQl help to build a result with custom aliased bool column |
Previous Message | Laurenz Albe | 2019-04-08 06:41:12 | Re: 10.2: high cpu usage on update statement |