Select if else

sakis · #1 (**permalink**) 12-02-09, 10:07

Hi i have a table named "prices"
it is like that

id prod_id price shopper_group
1---5-----10------1
2---5-----15------2
3---6-----20------1
4---7-----25------1

Some products have price for all the shopper groups some others not.

I d like to create a query that returns the prod_id of a certain shopper_group where price=myprice .
If there is not a price for that shopper_group return the product_id where price=myprice and shopper_group=1 (the default).

Is this possible through mysql?

r937 · #2 (**permalink**) 12-02-09, 11:14

Quote:

Originally Posted by sakis

Is this possible through mysql?

yes but you must specify which shopper_group you want ($mygroup) in addition to which price you want ($myprice)

Code:

SELECT COALESCE(selected.prod_id,default.prod_id) AS prod_id
     , COALESCE(selected.shopper_group,default.shopper_group) AS shopper_group
  FROM prices AS default
LEFT OUTER
  JOIN prices AS selected
    ON selected.price = default.price
   AND selected.shopper_group = $mygroup
 WHERE default.shopper_group = 1
   AND default.price = $myprice

sakis · #3 (**permalink**) 12-03-09, 10:09

Thanks

but it doesn't return any result.

Code:

SELECT COALESCE(selected.product_id,def.product_id) AS prod_id ,
COALESCE(selected.shopper_group_id,def.shopper_group_id)
FROM jos_vm_product_price AS def 
LEFT OUTER JOIN jos_vm_product_price AS selected 
ON selected.product_price=def.product_price  
AND selected.shopper_group_id = 6 WHERE def.shopper_group_id = 1 AND def.product_price< 250

Any idea?

sakis · #4 (**permalink**) 12-03-09, 10:28

It returns results now
The shopper_group_id=1 was deleted.

But it returns product_id's more than 1 times for all the user groups.

I used SELECT DISTINCT but has no effect

Is there a way to eliminate that?