Geek Answers Handbook

Request conditions for 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 duplicate entries because I need to store the vip value. If an ordinary person visits, I need to show

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

If a vip person comes, I have to show the price as

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

The last column is a boolean.

I need a request to get items. Please help get an inquiry for this.

+5
source share
2 answers

Regular client:

 select item, price from tablename where vip is NULL

Vip client:

 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)
+4
source

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 check it, but it should print the VIP price, if it exists, and the normal price otherwise. If these are doiesn't (which I doubt), you can add such a test to the where clause:

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

More articles:


All Articles