im not looking for direct answers but simple guidance to solve this.

 

i work as support for ERP software and in this database is 3 tables a customer individual price table (ie if that customer has his own prices for products then its here its stored)

group price tabel(ie if 10 customers are part of a group lets say builders and they pay 10% more for some products then its foundhere)

then standard prices

 

so lets say i have 10 products i have a standard price for each product

i have customer x who is part of the builders group

he has 4 special prices because i like him and 2 prices for the builders group.

 

so that means he pays standard price for 4 of the 10 products also.

 

i need to generate a report to give the customer a price list.

it should just list the 10 products and next to the product first if he has his own price if not then a group price and if not a standard price.

 

it doesnt seem too hard to get my head a round but im assuming i need a formula (using null values and certain joins).

the table linking confuses me because i would assume linking by product code, this column is common in all tables how ever not all products exist in each table

 

prods       stand $    grp $              cust $

1                $x                  

2                $x           $x                    $x

3                $x          

4                $x

5                $x           $x

6                $x                        

7                $x           $x                    $x

8                $x           $x

9                $x           $x                    $x

10              $x                                   $x

 

as you can see this is how the data exists in this database

 

any advice is greatly apprecated