Postgres - select logic

Question

I have the following data in my table A.

team | name | status | start_date | end_date
----------------------------------------------
A      Ann    Active  2016-01-01   2016-06-01
A      Linh   Active  2016-01-01   2016-06-01
A      John   Hold    2016-01-01   2016-06-01
A      Soph   Closed  2016-01-01   2016-06-01

I can do select from table A where status='Active' to get the active members.

Now, the requirement has changed to that if there at least one "Active" status member, I should look for both "Active" and "Hold" status members in the query.

If there is not "Active" status member, then I should NOT look for "Hold" status members.

How should I write in one SQL?

Does "at least one "Active" status member..." mean "if there's at least one record in the entire table with Active status..." (in the sample it's true - we have the very1st record with Active status) — Dmitrii Bychenko
– Dmitrii Bychenko, Commented Nov 9, 2017 at 15:41
...Or "at least one "Active" status member..." means if a person has a record with Active status you should provide records of this person with both Active and Hold statuses? (John should be excluded, he doesn't have any records with Active status) — Dmitrii Bychenko
– Dmitrii Bychenko, Commented Nov 9, 2017 at 15:48
@DmitryBychenko f there is not "Active" status member, then I should NOT include other status members leads me to different interpretation. Please author post expected result and add some sample data?.. — Vao Tsun
– Vao Tsun, Commented Nov 9, 2017 at 15:52

Hervé Piedvache · Accepted Answer · 2017-11-09 16:18:16Z

1

As I understand your requirement, this query is working :

SELECT *
  FROM tableA
 WHERE status IN ('Active', 'Hold')
   AND EXISTS (SELECT true FROM tableA WHERE status = 'Active');

Hope this help.

answered Nov 9, 2017 at 16:18

Hervé Piedvache

7984 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres - select logic

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related