CODEWITHSUNDEEP
postgresql
Anil V K Babu
Anil V K Babu

Reputation: 107

Ability to retrieve a different row based on different condition

I have a simple table with two columns:

ProductId Value
Global 1
P123 2
P234 3

I have to build a query which returns the corresponding value if the Product Id exists in the table, as in if P123 --> 2. Select Value from Product where ProductId='P123'

If we do not have the corresponding value for the Product Id, then return the value for the Global Product Id, as in for P456 we should return --> 1

Can you please let me know how we can perform the same in a single postgres query?

Upvotes: 0

Views: 40

Answers (3)

user330315
user330315

Reputation:

You can use a UNION

with data as (
  select value
  from product
  where productid = 'P123'
)
select *
from data
union all
select value
from product
where productid = 'Global'
  and not exists (select * from data);

The second part of the union will only be executed if the first one didn't return any results.

Upvotes: 0

Stefanov.sm
Stefanov.sm

Reputation: 13049

Use coalesce on scalar subqueries.

select coalesce
(
 (select value from t where productid = 'P123'), 
 (select value from t where productid = 'Global')
) as value;

Upvotes: 1

Julius Tuskenis
Julius Tuskenis

Reputation: 1610

You could filter corresponding and default products and make use of ORDER and LIMIT to select the corresponding if it exists. Something like that:

SELECT 
  value 
FROM 
  product
WHERE
  ProductId='P123' OR ProductId='Global'
ORDER BY
  CASE WHEN ProductId='Global' THEN 1 ELSE 0 END ASC
LIMIT
  1

The CASE clause is needed to position product with ID 'Global' last.

Upvotes: 0

Related Questions