Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SELECT JOIN Compare Fields 1

Status
Not open for further replies.

jrl237

IS-IT--Management
Joined
Jan 29, 2002
Messages
61
Location
US
I have a couple of tables, Orders and Products. The Orders table has ProductID and Quantity. The Products table has ProductID, Price, QuantityPrice, SalesPrice, and SalesQuantityPrice.

What I want to do is JOIN the two tables, and have the lowest price returned. The QuantityPrice and SalesQuantityPrice come into play if the Orders.Quantity > 1, otherwise Price or SalesPrice should be returned.

QuantityPrice, SalesPrice, and SalesQuantityPrice won't always exist. Sometimes the QuantityPrice might be lower than the SalesPrice.

Can this be done in a single SELECT statement? Could someone show me how, or at least point me in the direction of the syntax needed?

Any help greatly appreciated.

JRL
 
For joins:
Code:
SELECT     [fieldname1], [fieldname2], ... , [fieldnameN]
FROM       [primarytable]
INNER JOIN [secondarytable] on [secondarytable].[fieldnameX] = [primarytable].[fieldnameX]

For your query:
Code:
SELECT     o.productid, 
           o.quantity, 
           p.price, 
           p.quantityprice, 
           p.salesprice, 
           p.salesquantityprice
FROM       order o
INNER JOIN product p on p.productid = o.productid

Note: I am using alias table names (the o for order and p for product) to help make typing easier and to keep you from erroring out the script.

To have the max price displayed I assumed you are dealing with a numeric field. Give this a shot. It isn't the most elegant approach but worked in my testing with both blank spaces and NULLs.
Code:
SELECT       o.productid, 
             o.quantity, 
             CASE
               WHEN p.price > p.quantityprice and p.price > p.salesprice and p.price > p.salesquantityprice THEN p.price
               WHEN p.quantityprice > p.salesprice and p.quantityprice > p.salesquantityprice THEN p.quantityprice
               WHEN p.salesprice > p.salesquantityprice THEN p.salesprice
               ELSE p.salesquantityprice
             END
FROM         order o
INNER JOIN   product p on p.productid = o.productid

I hope this helps you out.



Krickles | 1.6180

 
Thanks Krickles. That's exactly what I needed. A star from me.

Thanks for the help, and the quick response.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top