CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.4
Narendra Sisodiya
Narendra Sisodiya

Reputation: 381

Query for conditions across rows

I have a table like this

id |   item |  price | vip
---+--------+--------+-----
0  |  tv    | 2000   | NULL
1  |  tv    | 2500   | TRUE
2  |  camera| 3000   | NULL
3  |  camera| 3500   | TRUE
4  |  phone | 1000   | NULL
5  |  pen   | 2      | NULL

In this table, I have some duplicated entries because I need to store vip price. If normal person visit, I need to show

item  | price
------+---------
tv    | 2000
camera| 3000
phone | 1000
pen   | 2

If vip person comes, then I should show price like

item  |  price
------+-----------
tv    | 2500
camera| 3500
phone | 1000
pen   | 2

The last column is Boolean.

I need query to get items. Please help to get the query for this.

Upvotes: 5

Views: 74

Answers (2)

user3844028
user3844028

Reputation:

All you need is a WHERE clause

 SELECT price from *** WHERE ((VarVip = true AND vip IS NOT NULL) OR (vip IS NULL)) and item = ...

I can't test it but it should print the VIP price if it exists and the normal price otherwise. If it doiesn't ( which I doubt ) you can still add a test like this in the where clause : 

exists(SELECT price from *** where vip IS NOT NULL and item = ...)

Upvotes: 0

jarlh
jarlh

Reputation: 44776

Regular customer:

select item, price
from tablename
where vip is NULL

vip customer:

select item, price
from tablename t1
where vip is true
   or not exists (select 1 from tablename t2
                  where t1.item = t2.item
                    and vip is true)

Upvotes: 4

Related Questions