Search the Community

hey guys i've made alterations to my query below, previously it had a lot of sub queries which was unnecessary as it can all be achieved by a join instead. but this query brings back the wrong highest_bidder and highest_bidders_id which is brought from my bid and users table if i do a simple query like this: SELECT b.bid_id, u.user_id, u.username, b.price FROM bids b LEFT JOIN users u ON u.user_id = b.user_id ORDER BY b.price DESC, b.created_timestamp DESC, b.bid_id DESC LIMIT 1 than i get the correct results as i should with this big query below. SELECT i.item_id, i.user_id, i.title, i.buy_now, i.auction, i.condition, i.offers_accepted, i.free_delivery, i.free_delivery_condition, i.collection, i.collection_only, i.p_and_p, i.buy_now_price, i.quantity, i.description, (i.quantity - count(ip.item_id)) AS `quantity_available`, COUNT(ip.item_id) AS `quantity_sold`, io2.offer_price, u.username AS `seller`, DATE_FORMAT(u.timestamp, '%d/%m/%y') AS `member_since`, @bid_increment := bi.increment AS `bid_increment`, @current_auction_price := IF (b.price IS NULL, i.starting_price, b.price) AS `current_auction_price`, TRUNCATE((@current_auction_price + @bid_increment), 2) AS `minimum_bid`, ua.town_city, ua.country, ua.continent, u2.username AS `highest_bidder`, u2.user_id AS `highest_bidder_id`, io2.quantity AS `quantity_offer`, LOWER(c.country_code) AS `sellers_country_code`, COUNT(DISTINCT io.item_offer_id) AS `offer_count`, CONCAT(FORMAT(((SUM(uf.positive + uf.neutral) - uf.negative) / count(uf.user_feedback_id) * 100), 2), '%') AS `sellers_feedback_percentage`, ROUND(SUM((uf.positive * 1) + (uf.neutral * 0.5)) - (uf.negative * 1), 0) AS `sellers_feedback_score`, COUNT(DISTINCT b.bid_id) AS `bid_count`, COUNT(DISTINCT uiw.user_item_wish_id) AS `wish_count`, COUNT(DISTINCT uiwa.user_item_watch_id) AS `watch_count`, CONCAT_WS(', ', ua.town_city, ua.county, ua.country) AS `location`, NOW() AS `server_time`, @timestamp := CONVERT_TZ(DATE_ADD(i.start_timestamp, INTERVAL concat(i.listing_duration) DAY), '+00:00', '+00:00') AS `timestamp`, DATE_FORMAT(@timestamp, '%D, %M at %h:%i %p') AS `end_timestamp`, (SELECT COUNT(reported_item_id) FROM reported_items WHERE item_id = i.item_id AND user_id = :user_id) AS `reported`, (SELECT COUNT(item_id) FROM user_item_wishes WHERE item_id = i.item_id AND user_id = :user_id) AS `wished`, (SELECT COUNT(item_id) FROM user_item_watches WHERE item_id = i.item_id AND user_id = :user_id) AS `watched`, (SELECT COUNT(user_follower_id) FROM user_followers WHERE user_id = i.user_id AND follower_id = :user_id) AS `followed`, (SELECT COUNT(DISTINCT(ip_address)) FROM statistics WHERE DATE_ADD(visited, INTERVAL 2 HOUR) >= now()) AS `period_last_visited`, (SELECT COUNT(DISTINCT(ip_address)) FROM statistics WHERE uri = '/item/1') AS `views`, IF (i.start_timestamp >= now(), true, false) AS `ended`, CASE WHEN (:latitude IS NOT NULL AND :longitude IS NOT NULL AND u.latitude IS NOT NULL AND u.longitude IS NOT NULL) THEN @distance := (SELECT (IF(:distance_unit = 'Kilometers', 6371, 3959) * 2 * ASIN(SQRT(POWER(SIN((:latitude- u.latitude) * pi()/180 / 2), 2) + COS(:latitude * pi()/180) * COS(u.latitude * pi()/180) * POWER(SIN((:longitude - u.longitude) * pi()/180 / 2), 2))))) END, @distance_unit := IF (@distance >= 1, IF (@distance < 2, REPLACE (:distance_unit, 's', ''), @distance_unit), :distance_unit), IF (@distance, CONCAT(TRUNCATE(@distance, 0), space(1) , @distance_unit), 'Unknown Distance') AS `distance` FROM items i LEFT JOIN users u ON u.user_id = i.user_id LEFT JOIN item_purchases ip ON ip.item_id = i.item_id LEFT JOIN user_item_wishes uiw ON uiw.item_id = i.item_id LEFT JOIN user_item_watches uiwa ON uiwa.item_id = i.item_id LEFT JOIN user_addresses ua ON ua.user_id = i.user_id LEFT JOIN item_addresses ia ON ia.user_address_id = ua.user_address_id LEFT JOIN countries c ON c.country_name = ua.country LEFT JOIN item_offers io ON io.item_id = i.item_id LEFT JOIN users_feedback uf ON uf.user_id = i.user_id LEFT JOIN item_offers io2 ON io2.user_id = i.user_id LEFT JOIN bids b ON b.item_id = i.item_id LEFT JOIN users u2 ON u2.user_id = b.user_id LEFT JOIN bid_increments bi ON b.price BETWEEN bi.price_from AND bi.price_to WHERE i.item_id = :item_id AND ia.user_address_id = i.user_address_id AND uf.seller = '1' ORDER BY b.price DESC, b.created_timestamp DESC, b.bid_id DESC the joins and the order by are correct so i dont understand why the query is producing the wrong highest bidder if anyone know why please? also any general advice on this query, how i should do things would be greatly welcomed. thank you