I’m having trouble getting a correlated subquery to work (it’s the first time that I’ve used correlated subqueries). There’s two tables, one records auctions and the other records bids. Not all auctions will have any bids, the intended output will be three columns, auction_id, bidder (the ID of the highest bidder else 0) and the highest bid (with 0 being returned if there were no bids).
The idea being that the current tick gets used in the main WHERE CLAUSE to get all auctions that end that tick and for each one get the info mentioned above. The query I’ve currently got is:
SELECT
CASE WHEN
bids.bidder IS NULL
THEN 0
ELSE bids.bidder
END AS bidder
, CASE WHEN
bids.gold_bid IS NULL
THEN 0
ELSE bids.gold_bid
END AS gold_bid
, auction.id AS auction_id
FROM
ue_auction_bids AS bids
RIGHT OUTER JOIN
ue_auction AS auction
ON bids.auction_id = auction.id
WHERE
gold_bid =
(
SELECT
max(gold_bid) AS max_bid
FROM
ue_auction_bids AS bids
WHERE
auction_id = bids.auction_id
)
AND
auction.end_tick = 50
That gives me,
bidder gold_bid auction_id
2 153410 1
I’m not sure if it’s possible to do it in 1 query (I want to try and do it in one query to having to send unnecessary queries between PHP and MySQL, I know that both will in reality be on the same server box).
Table structure and test data for both tables
CREATE TABLE IF NOT EXISTS `ue_auction` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`con_lister` int(11) NOT NULL,
`start_gold` int(11) NOT NULL,
`start_tick` int(11) NOT NULL,
`end_tick` int(11) NOT NULL,
`comments` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;
--
-- Dumping data for table `ue_auction`
--
INSERT INTO `ue_auction` (`id`, `con_lister`, `start_gold`, `start_tick`, `end_tick`, `comments`) VALUES
(1, 3, 200, 1, 50, ''),
(2, 3, 200, 1, 50, ''),
(3, 3, 200, 1, 50, '');
CREATE TABLE IF NOT EXISTS `ue_auction_bids` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`auction_id` int(11) NOT NULL,
`gold_bid` int(11) NOT NULL,
`bidder` int(11) NOT NULL,
`bid_tick` int(11) NOT NULL,
`bid_date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=26 ;
--
-- Dumping data for table `ue_auction_bids`
--
INSERT INTO `ue_auction_bids` (`id`, `auction_id`, `gold_bid`, `bidder`, `bid_tick`, `bid_date`) VALUES
(1, 1, 200, 1, 2, '2011-03-07 19:53:56'),
(2, 1, 230, 0, 5, '2011-03-13 08:49:27'),
(3, 1, 265, 1, 5, '2011-03-13 08:53:11'),
(4, 1, 500, 1, 5, '2011-03-13 08:54:04'),
(5, 1, 575, 1, 5, '2011-03-13 08:54:33'),
(6, 1, 662, 1, 5, '2011-03-13 08:55:10'),
(7, 1, 762, 1, 5, '2011-03-13 09:03:23'),
(8, 1, 877, 1, 5, '2011-03-14 02:22:15'),
(9, 1, 1009, 1, 5, '2011-03-14 02:22:30'),
(10, 1, 1161, 1, 5, '2011-03-14 02:23:07'),
(11, 1, 1336, 1, 5, '2011-03-14 02:23:37'),
(12, 1, 1537, 1, 5, '2011-03-14 02:23:48'),
(13, 1, 1768, 1, 5, '2011-03-14 02:24:00'),
(14, 1, 2034, 1, 5, '2011-03-14 02:24:25'),
(15, 1, 2340, 0, 5, '2011-03-14 08:18:30'),
(16, 1, 2691, 0, 5, '2011-03-14 08:18:40'),
(17, 1, 3095, 1, 5, '2011-03-14 08:21:17'),
(18, 1, 4000, 1, 5, '2011-03-14 08:21:26'),
(19, 1, 5000, 1, 5, '2011-03-14 08:21:30'),
(20, 1, 6000, 2, 5, '2011-03-16 19:43:50'),
(21, 1, 70000, 2, 5, '2011-03-16 19:43:57'),
(22, 1, 100000, 2, 5, '2011-03-16 19:44:18'),
(23, 1, 116000, 1, 5, '2011-03-21 08:44:22'),
(24, 1, 133400, 1, 5, '2011-03-21 08:44:38'),
(25, 1, 153410, 2, 5, '2011-03-23 19:04:19');