Bros having some probs with this, so pointers greatly appreciated
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
then 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