QUERY FOR PROXY

[gsc123]

Hi, I've place this question here because its relavant to this forum... sorry about the other post

I need the following query

I use this query to get most of the information, the price is updated in two tables, one bidhistory and the other the products, so I need somewhere in this select query to pull out min bid for users below the proxy bid from the bidhistory

ie
(select min(bidAmount) from bidhistory INNER JOIN products ON products.idproduct = bidhistory.idproduct) as price

but my select to get everything about the item is:

SELECT products.Description, products.Price, products.ImageUrl, products.idproduct, products.endDate, suppliers.idsupplier, suppliers.supplierName FROM suppliers INNER JOIN products ON suppliers.idsupplier = products.idsupplier WHERE idcategory=21 and ListHidden =
false


EG:

is my structure wrong, because bidhistory is only a history of bids not the products price initially laid out, its the start price say £200 ... if theres no bids then the history does'nt have a record so I need the products.price if there is no bids or to show min price from bidhistory...

I then would need that users highest bid from bidhistory so they can see what higher proxy bid they have entered

I hope this is clear.. many thxs in advance

 

[MartinCroft]

To check I understand what you are asking. You want the minimum bid amount from the bid history for each product, and if that does not exist then the products price instead

 

[gsc123]

Thats correct yes -- so far I have these to queries, to save a lot of bother I would like to marry them together if poss - I added a third, my max bid...

1. - gets most of the details
SELECT description, price, sku, details, startPrice, imageurl, stock, idCategory, startDate, endDate, idsupplier FROM products WHERE idproduct=1559 AND active=-1


2. - gets min bid


SELECT Min(bidAmount) AS price FROM BidHistory WHERE (((BidHistory.bidAmount) In (SELECT Min(BidAmount) FROM BidHistory WHERE BidAmount<10009999 and idproduct = 1559)));

3. gets the max bid
SELECT Max(bidAmount) AS price
FROM BidHistory
WHERE (((BidHistory.bidAmount) In (SELECT Max(BidAmount) FROM BidHistory WHERE BidAmount<10009999 and idproduct = 1559 and idcustomerbid=15)));


Each select could use the customerid because its that person logged on...

 

[gsc123]

Here they are a bit more readable

SELECT description, price, sku, details, startPrice, imageurl, stock, idCategory, startDate, endDate, idsupplier FROM products WHERE idproduct=1559 AND active=-1

SELECT Min(bidAmount) AS minPrice FROM BidHistory WHERE (((BidHistory.bidAmount) In (SELECT Min(BidAmount) FROM BidHistory WHERE BidAmount<10009999 and idproduct = 1559)));

SELECT Max(bidAmount) AS maxPrice FROM BidHistory WHERE (((BidHistory.bidAmount) In (SELECT Max(BidAmount) FROM BidHistory WHERE BidAmount<10009999 and idproduct = 1559 and idcustomerbid=15)))

 

[MartinCroft]

One potential option is, use a derived table and COALESCE to bring back either the bid price or if it doesn't exist ( NULL) then the price of the product. If you see what I mean

SELECT 
	*
FROM
	Products MainProducts
JOIN

(
		 

SELECT 
	products.idproduct,
	MIN(COALESCE(bidAmount,Products.Price)) MINPrice,
	MAX(COALESCE(bidAmount,Products.Price)) MAXPrice
from 
	products
LEFT JOIN 
	bidhistory ON products.idproduct = bidhistory.idproduct 
GROUP BY 
	products.idproduct
)Price ON Price.idproduct =MainProducts.idproduct

 

[gsc123]

will this only work on sql server?

 

[MartinCroft]

Is it not SQL Server your running on? It will work on SQL 2000/05/08, I imagine it would need to be tweaked for Oracle but I am unsure. I thought it was SQL being in the SQL forumn.

 

[gsc123]

Well you aint gonna like this at present but at the mo using access - AHHHHHHHHHHHHHHHHHHHHHH!

yeh, I know but moving to server once front end is done so should I wait or crete mutiple queries as above to get what I need?