sql query question

  • 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.

  • 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

  • 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

     

  •  

    ----------------------

    ------DDL-------------

    ----------------------

     

    create

    table customer(

    CustomerID

    int,

    CustomerName

    varchar (20),

    Email

    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

    www.sql-library.com[/url]

  • 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