Can somebody please help me with this query. The problem is that it does not return the correct number of hits on the item when there are multiple images related to the same item in the image table.
Your help will be greatly appreciated:
SELECT i.item_id, i.item_owner_id, l.link_item_quantity, l.link_item_initquantity, i.item_submitted_date,
i.item_expiry_date, i.item_name, i.item_notes, i.item_buynow_price, i.item_startbid_price, i.item_highlight, m.image_thumbnail,
MAX( b.bid_amount ) AS lastbid, COUNT( h.hit_id ) AS hits
FROM items i
LEFT OUTER JOIN hitcounter h ON h.hit_item_id = i.item_id
LEFT OUTER JOIN images m ON m.image_linked_id = i.item_id
LEFT OUTER JOIN bids b ON b.bid_item_id = i.item_id
LEFT OUTER JOIN users u ON u.user_id = i.item_owner_id
LEFT OUTER JOIN shopitemlink l ON l.link_item_id = i.item_id
WHERE u.user_id = '3'
AND i.item_viewable = '1'
AND i.item_expiry_date > now( )
AND l.link_shop_id IS NOT NULL
GROUP BY i.item_id
ORDER BY i.item_submitted_date DESC
Edit: i just had to put 'DISTINCT' in the COUNT(), like COUNT(DISTINCT h.hit_id )
