CODEWITHSUNDEEP
postgresql
pixelhandler
pixelhandler

Reputation: 625

Create a column in a table view with a value based on a group of rows in another table

I'd like to create a table view and include a new column that is based on values from another table.

Many rows of table B belong to one row of table A.

Table B has a status column (with values like active, completed, etc) and a foreign key (for table A).

In the new table view (for A) I want to create an active column (true / false) that is based on any related rows in table B having a status value of active and a matching foreign key.

Upvotes: 0

Views: 42

Answers (1)

Steven
Steven

Reputation: 15258

If it is just about checking if the value exists, then this should do the job

select  A.c1, 
        A.c2, 
        -- other columns from A
        case when exists (select 1 from B_Table B where A.FK = B.FK and B.status = 'active') 
            then 'true'
            else 'false'
        end as Active
from A_Table B

Upvotes: 1

Related Questions