Comparison Query Access/SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Comparison Query Access/SQL

Author

Topic

barney
Starting Member

2 Posts

Posted - 2002-04-05 : 12:16:41

I am trying to create a query to list all customers that have a particular product at a higher price than another product.
Each product description consists of several fields ie ProductPrice, ProductSize, ProductVariant, ProductFamily, ProductManufacturer. There are two other fields that are required for the query Country and OutletName. A plain text example would be:
Show all Outlets by Country where "Starbys(Manuf)BigBlueBerry(Fam) Muffins(Var) 100gm(Size)" retail price is greater than the retail price of "Acme BlueGiant Muffins 100gm"

Can someone point me in the right direction as I am a novice at this
Thanks
Barney

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-05 : 12:40:08

I am assuming that multiple Outlets in multiple Countries could sell Acme BlueGiant Muffins 100gm.

How do you determine the retail price of Acme BlueGiant Muffins 100gm then?

You could do this. Which should return to you a list of all 'Acme BlueGiant Muffins 100gm' in all outlets by country with a comparison of all outlets and countries that have a higher product price


SELECT p.*, p2.*
FROM Products p 
INNER JOIN Products p2 
     ON (p.ProductPrice > p2.ProductPrice
         AND p.ProductSize = p2.ProductSize
         AND p.ProductVariant = p2.ProductVariant)
WHERE p2.ProductSize = '100gm'
     AND p2.ProductVariant = 'Muffins' 
     AND p2.ProductFamily = 'BlueGiant' 
     AND p2.Productmanufacturer = 'Acme'
ORDER BY p.Country, p.Outlets ASC

Edited by - yakoo on 04/05/2002 12:41:29

barney
Starting Member

2 Posts

Posted - 2002-04-05 : 15:30:25

(I am assuming that multiple Outlets in multiple Countries could sell Acme BlueGiant Muffins 100gm.)Yes this is true, but more importantly outlets can sell both products BigBlueBerry and Bluegiant. It is in those outlets where there is a price differential, within the same outlet, irrespective of the actual price I need to get returned in the query.
(How do you determine the retail price of Acme BlueGiant Muffins 100gm then?)There is a ProductPrice field for all products

Does this make sense?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources