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";