September 12, 2006 at 3:23 pm
Hi all,
I have three tables. Customer, Auction and Products
Customer
--------------
CustomerID [int]
CustomerName [varchar]
Email [varchar]
Products
-------------
ProductID [int]
ProductName [varchar]
ReservePrice [numeric]
CurrentPrice [numeric]
Type [varchar]
AuctionRefID [int]
Auction
-----------
AuctionID [int]
CustomerID [int]
ProductID [int]
AuctionRefID [int]
AuctionPrice [numeric]
My db rows looks like this:
CustomerID CustomerName Email
---------------- ---------------------- ---------
1 SimonTest SimonTest@SimonTest.com
2 JohnTest JohnTest@JohnTest.com
ProductID ProductName ReservePrice CurrentPrice Type AuctionRefID
-------------- -------------------- ------------------- ------------------ ------- -------------------
1 Shoe 100 110 Auc 100
2 Socks 200 205 Auc 110
3 Jacket 220 210 Auc 120
AuctionID CustomerID ProductID AuctionRefID AuctionPrice
1 1 1 100 110
2 2 1 100 100
3 1 2 110 205
4 2 3 120 210
The Query:
From the above tables, I want to retrieve the customer bids in which the customer is winning. For example: I want to retrieve rows like this.
CustomerName ProductID ProductName CurrentPrice
---------------------- ------------- -------------------- ------------------
SimonTest 1 Shoe 110
SimonTest 2 Socks 205
JohnTest 3 Jacket 210
Any help with SQL Query please? I tried for some time and eventually gave up. By the way, this is not my database design. I'm working on someone else db design.
Please let me know.
September 12, 2006 at 3:51 pm
I don't see what's wrong with database design. Whoever did it he did it right.
But I cannot see in those tables what does it mean "the customer is winning".
Can you explain it in database terms? What in those tables shows me which customer is winning the auction?
_____________
Code for TallyGenerator
September 12, 2006 at 4:05 pm
Hope this helps:
Select Auction.AuctionRefID,AuctionRefID.AuctionPrice,
Customer.CustomerName,Products.ProductName
from Auction INNER JOIN
(
select AuctionRefID,max(AuctionPrice) as MaxPrice,min(AuctionID) as MinAuctionID
from Auction
Group by AuctionRefID ) WinningBid on Auction.AuctionRefID = WinningBid.AuctionRefID
and Auction.AuctionID = WinningBid.MinAuctionID
INNER JOIN Customer ON Auction.CustomerID = Customer.CustomerID
INNER JOIN Products ON Auction.ProductID = Products.ProductID and Auction.AuctionRefID = Products.AuctionRefID
I assumed that if 2 person bids the same price the first guy wins. Secondly if you need reserve price to be met you can modify the query to have it in the where clause(where ReservePrice <= MaxPrice)
Thanks
Sreejith
September 12, 2006 at 5:28 pm
----------------------
------DDL-------------
----------------------
create
table customer(
CustomerID
int,
CustomerName
varchar (20),
varchar (50)
)
create
table Products
(
ProductID
int,
ProductName
varchar (50),
ReservePrice
int,
CurrentPrice
int,
Type
varchar (50),
AuctionRefID
int
)
create
table Auction
(
AuctionID
int,
CustomerID
int,
ProductID
int,
AuctionRefID
int,
AuctionPrice
int
)
-------------------------
----Populate Test Data---
-------------------------
insert
customer
select
1, 'SimonTest', 'SimonTest@SimonTest.com' union all
select
2, 'JohnTest', 'JohnTest@JohnTest.com'
insert
Products
select
1, 'Shoe' ,100 ,110, 'Auc', 100 union all
select
2 ,'Socks', 200 ,205, 'Auc' ,110 union all
select
3 ,'Jacket' ,220 ,210, 'Auc', 120
insert
Auction
select
1, 1 ,1, 100 ,110 union all
select
2, 2, 1, 100 ,100 union all
select
3, 1, 2, 110, 205 union all
select
4, 2, 3, 120, 210
---------------------------------------------
---------------------------------------------
-----------------------------------------------
---------------The Code-------------------------
------------------------------------------------
select
c.CustomerName, x.ProductID, p.ProductName, x.CurrentPrice
from
products p
join
(
select a.ProductID, max(AuctionPrice)CurrentPrice,
rtrim(
substring(
max(
cast(auctionprice as varchar(5)) + '@' + cast(a.Customerid as varchar(5))),
patindex('%@%',max(cast(auctionprice as varchar(5)) + '@' + cast(a.Customerid as varchar(5))))+1,
50
)) as CustomerID
from Auction a
group by a.ProductID
)X
on p.productid = x.productid
join
customer c
on c.CustomerID = x.CustomerID
September 12, 2006 at 5:38 pm
Thanks Jules and Sreejith. Both your sqlscripts is what I was looking for.
Jules, Thanks for spending some time and creating the sqlscripts for data structure, data and the solution query for my problem.
Its very much appreciated.
Many thanks once again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply