Mysql – Correct way to write this query

MySQL

I'm trying to write a query that basically is searching by price, though sometimes the items may have a discount on them and this price isn't stored in the DB, only the discount percent and dates the discount runs between are, so we have to work out the price of the item on the fly to see if it should be included.

Additionally, if the item is an auction we ignore the sale price and just look at the start_price and max_bid to work out the current price (max bid is only set if the item has at least 1 bid).

We know it is NOT an auction if start_price == buyout_price, so know we have to figure out if the item is on sale or not and if so calculate the sale price to see if to include it in their requested price range.

I have this code..

SELECT
    whatever
FROM
    auctions AS a
LEFT JOIN
    ..............
WHERE
    ..............
AND
    IF(a.start_price == a.buyout_price AND a.discount_start_date < UNIX_TIMESTAMP() AND a.discount_end_date > UNIX_TIMESTAMP() AND a.discount > 0, (a.buyout_price - ((a.buyout_price / 100) * a.discount)), IF(a.max_bid > a.start_price, a.max_bid, a.start_price)) >= '500' AND IF(a.start_price == a.buyout_price AND UNIX_TIMESTAMP() > a.discount_start_date AND UNIX_TIMESTAMP() < a.discount_end_date AND a.discount > 0, (a.buyout_price - ((a.buyout_price / 100) * a.discount)), IF(a.max_bid > a.start_price, a.max_bid, a.start_price)) <= '700'
AND
    ..............;

As you may be able to make out I'm trying to get all results with prices between 500 – 700.

I'm also getting a error which appears to have something to do with the UNIX_TIMESTAMP(), though not sure why!?

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== a.buyout_price AND  a.discount_start_date < UNIX_TIMESTAMP() AND a.discount_e' at line 1

Best Answer

Write yourself a view that produces, in a single column, the value of price that you're interested in. It might be a UNION of a few different criteria. When you're done you'll have a view of all items with two columns: the id and the price.

Join that view to whatever else you need (perhaps including the original price), and voila.

Tip: skip IF and just use AND and OR, per the SQL standard. And eschew LEFT JOIN unless you know -- and know why -- you can't use a proper inner join. If the database is pervasively unreliable in that regard, it will lead to more pain than all the outer joins in the world can alleviate.

Related Solutions

Mysql – Simple select statement not returning correct data

You are missing a JOIN between prices and items.

Your implicit join in the where clause only joins vendors and prices so you get a cartesian join for the result of that join and the items table. If you had used ANSI JOIN keywords instead, you would have noticed that immediately

You need something like this:

select distinct items.name, 
       vendors.name, 
       prices.url, 
       prices.price 
 FROM vendors
   JOIN prices ON vendors.vid = prices.vid 
   JOIN items ON items.iid = prices.iid ---  this part is missing
 WHERE items.iid='3' 
ORDER BY prices.price;

I don't know if the join condition is correct, because you didn't tell us which column in prices contains the foreign key to items.

MySQL database design

I'll take a crack at this. Note, that I've just listed fields that are necessary. If you have additional data you want to store for in those tables, feel free to add them (so long as they are normalized).

While you did state that you would use a different database for each region, I designed this so that they could all work in one database. Right now you may have only 5 regions, but this offers the flexibility to easily add more:

table: region
regionID        varchar(2)
regionName      varchar(50)

Defines data for each realm. I went with a surrogate key (realmID) because it's possible that realmName might not be unique. This assumes that each realm can only belong to one region (regionID needs to be a foreign key to regionID on the region table):

table: realm
realmID         int
regionID        varchar(2)
realmName       varchar(50)

Defines data for each user (seller/buyer). I decided that it would be better if it had a surrogate key (sellerID) because seller/buyers first/lastname combinations may not be totally unique. Note, you may also want an address table to store multiple:

table: user
userID          int
userFirstName   varchar(20)
userLastName    varchar(20)
userEmail       varchar(100)

Data for each auction. AuctionID would be a auto-generated ID and the Primary Key. You would also want foreign keys on realmID (realmID on realm table) and sellerID (userID on user table):

table: auction
auctionID       int
realmID         int
sellerID        int
auctionPrice    decimal
auctionStart    datetime

Data for each auction that has been completed with a foreign key on buyerID (userID on user table):

table: auctionHistory
auctionID       int
buyerID         int
finalPrice      decimal
auctionFinished datetime

I don't think that you'd need a seperate table for "Scan". That would be a simple query of rows in the auction table that do not have matching rows in the auctionHistory table. Hopefully, this will help you get started. If I missed/misread anything, please let me know.

Best Answer

Related Solutions

Mysql – Simple select statement not returning correct data

MySQL database design

Related Question