I'm trying to do a query that if mike isn't in the three highest bids for a keyword, then select the row(row that has been outbid). I tried below but i get no results, i should get rows with id 4 and 7. btw a keyword can be bidded on more than once.
$construct = "SELECT * FROM `temp-advertise` WHERE username='mike' AND bid < (SELECT min.bid FROM `temp-advertise` min LEFT JOIN `temp-advertise` min2 on min.keyword=min2.keyword WHERE min.username='mike' ORDER BY bid DESC LIMIT 2,1)"; id | username| keyword | bid | 1 | mike | one | 7 | 2 | tomm | one | 4 | 3 | cedr | one | 6 | 4 | mike | two | 1 | 5 | tomm | two | 5 | 6 | harr | two | 5 | 7 | mike | one | 3 | 8 | harr | two | 3 |
i tried another query and also get no results
$construct = "SELECT child.* FROM `temp-advertise` child LEFT JOIN `temp-advertise` parent on child.keyword=parent.keyword WHERE child.username='mike' GROUP BY child.keyword ORDER BY child.bid DESC LIMIT 2,999";