Datagrid needed pulling from 2 tables

[John Cassell]

Hi There,

can someone help me achieve this pls..

I need a datagrid where I can load data so it looks like the image below.

This would be no problem but I need a small change. There is an "product" table with a column for Item, Name and Price. The price refers to a standard fixed price. However, some customers will have a special price which differs from this, so I guess I need a new table called "customerprices". How do I then get it to check the customer and load either a) the standard price or b) the special price (if one exists)?

Any help would be greatly appreciated.

Thanks

John

 

[jonmarlow]

I would usually send 'the customer' to a stored procedure which looks something like

SELECT 
a.item,
a.name,
isnull(b.price,a.price),
a.quantity

FROM 
products a 
LEFT JOIN customerprices b on b.item = a.item and b.customer = @customer

The following assumptions are made about this example:
1. @customer is the name of the customer passed to the procedure.
2. The products table contains the columns: Item, Name, Price and Quantity.
3. The customerprices table contains the columns: Customer, Item, Price


Using this procedure, if there is an entry in the customerprices table for a particular item and customer combination, then that price takes preference over the standard price from the products table.

 

[John Cassell]

Hi John,

thanks very much for the reply. I worked out another way around this already (can't remember exactly off the top of my head, think it was CONSEACE function or something like that). Should have posted that information to save you wasting time, so sorry about that.

Appreciat the reply none the less.

Thanks

John

 

[cjard]

something like this:

SELECT 
  g.id,
  g.name,
  COALESCE(s.specialPrice, g.genericPrice, -999.99) as price
FROM
  genericPrices g
  LEFT OUTER JOIN
  specialPrices s
  ON
    s.id = g.id AND
    s.customerID = @customerID

customerID being the one to get prices for

theory:

generic prices and spoecial prices are joined using the id and the customer ID

if the customer doesnt exist in specialprices or the id of the product doesnt exist, the s.specialPrice will be NULL

COALESCE takes a list of arguments and returns the first one that is not null

thus if your special price isnt null, you get that price, otherwise you get the generic price, otherwise you get -999.99. -999.99 is NOT NEEDED i just put that in there to demo that coalesce can a) take a constant, b) take more than 2 values

You can leave it out and if generic price is null you get null