By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,970 Members | 1,193 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,970 IT Pros & Developers. It's quick & easy.

Challenge: complex sql instruction?

P: 2
I'll explain my problem using a little example

table
article name price
-------------------------------
art1 Default 10
art2 Default 15
art2 Egmond 12
art3 Default 20
art3 Windels 17

Goal:
Article prices are saved with customer name Default. When one customer has a specific price, the customer's name is used (ex. customer Egmond can buy article art2 for 12 ; other customers have to pay 15 ).
If a customer has no specific price for an article, the default price must be shown.

Using one query, I should be able to get the catalog for customer Egmond:
art1 10
art2 12
art3 20

Can somebody help me?
Nov 23 '07 #1
Share this Question
Share on Google+
1 Reply


amitpatel66
Expert 100+
P: 2,367
I'll explain my problem using a little example

table
article name price
-------------------------------
art1 Default 10
art2 Default 15
art2 Egmond 12
art3 Default 20
art3 Windels 17

Goal:
Article prices are saved with customer name Default. When one customer has a specific price, the customer's name is used (ex. customer Egmond can buy article art2 for 12 ; other customers have to pay 15 ).
If a customer has no specific price for an article, the default price must be shown.

Using one query, I should be able to get the catalog for customer Egmond:
art1 10
art2 12
art3 20

Can somebody help me?
Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT * from table1 WHERE name = 'Default' AND article NOT IN(SELECT aricle from table1 WHERE name = 'Egmond')
  3. UNION
  4. SELECT * from table1 WHERE name = 'Egmond'
  5.  
  6.  
  7.  
Nov 25 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.