CODEWITHSUNDEEP
phpmysql
Rituparno
Rituparno

Reputation: 23

get product price based on customer code

My Product table looks like 

ID     Type          Product Code     Product Name     Price
1      all customers 56620000         Product A        219.68 €
2      all customers 56621000         Product B        4,351.91 €
3      all customers 56622000         Product C        110.98 €
4      155000        56622000         Product C        100.00 €
5      all customers 56626000         Product D        2,410.38 €
6      all customers 56772000         Product E        100.00 €
7      160000        56772000         Product E        90.00 €

If you notice row 3,4 and 6,7 has same product code but with different type and price. That means a product can have a price for all customer as well as for few specific customers. If a customer with customer id 155000 performs a search with product code 56622000, that customer will get price 100.00 € (see row number 4) not 110.98 € (see row number 3). But if that same customer performs a search with product code 56772000, he/she will get price 100.00 €(see row number 6) not 90.00 €(see row number 7). Because there is no specific price for that user for product code 56772000.

My Query: How to execute this operation using PHP and MySql from a single table.?

Upvotes: 2

Views: 217

Answers (3)

user1646111
user1646111

Reputation: 

SELECT * FROM 
   (SELECT * FROM product
      WHERE `Type` = '155000' and ProductCode = '56622000'
      LIMIT 1
    UNION
    SELECT * FROM product
      WHERE `Type` = 'all customers' and ProductCode = '56622000'
      LIMIT 1) a
LIMIT 1;

Thanks for @491243 for the fiddle code.

Upvotes: 0

Jazi
Jazi

Reputation: 6752

SELECT * 
FROM Products 
WHERE ProductCode = 'product_id' AND Type IN ('customer_id', 'All Customers') 
ORDER BY Type 
LIMIT 1

Upvotes: 0

John Woo
John Woo

Reputation: 263803

SELECT  *
FROM    product
WHERE   ProductCode = 'x' AND
        Type IN ('y', 'All Customers')
ORDER   BY FIELD(Type, 'y', 'All Customers')
LIMIT   1

Replace x and y with your desired value shown in the demo.

Upvotes: 2

Related Questions