CODEWITHSUNDEEP
sqloracle-databasepostgresql
user630209
user630209

Reputation: 1207

Query to fetch superset if subset combination returns null result?

TABLE : **PROFILE**
RUN_ID| Type | code | gender | vacId

1     | A    | biz  | M      | 110
2     | A    | pro  | M      | 113
3     | A    | tot  | F      | 114

Required to return the vacId, based on the input if I input, if code+type combination is not present then return only vacId which equals to given type

  1. TYPE=A AND CODE=BIZ AND GENDER=M , then it should return 110
  2. TYPE=A AND CODE=SYS then should return 110 & 113 , 114 there is no A+SYS combination so returned superset A's data

How can I write a query for this which should work in pgsql and oracle for now.

Is there a way that I can achieve this using COALESCE

Upvotes: 1

Views: 709

Answers (1)

klin
klin

Reputation: 121654

You can use conditional where clause, e.g. ($ used for parameters): 

select p.*
from profile p
where case
    when exists (
        select 1
        from profile
        where type = $type and code = $code and gender = $gender
        )
    then type = $type and code = $code and gender = $gender
    else type = $type
    end;

Update.

An alternative solution could be to get results in a single json object, what will enable you to use coalesce():

select coalesce(
    (select jsonb_agg(to_jsonb(p))
    from profile p
    where type = $type and code = $code and gender = $gender),
    (select jsonb_agg(to_jsonb(p))
    from profile p
    where type = $type)
);

Upvotes: 1

Related Questions