mysql subquery not working.

[MDanz]

I'm trying to do a query that if mike isn't in the three highest bids for a keyword, then display 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='$username' GROUP BY child.keyword ORDER BY child.bid DESC LIMIT 2,999";

[smoseley]

SELECT `ta`.*, COUNT(`ta2`.`id`) AS `outbids`

FROM `temp-advertise` AS `ta`

INNER JOIN `temp-advertise` AS `ta2`

    ON `ta`.`keyword` = `ta2`.`keyword`

    AND `ta`.`bid` < `ta2`.`bid`

    AND `ta`.`username` != `ta2`.`username`

WHERE `username` = 'mike'

GROUP BY `ta`.`id`

HAVING `outbids` >= 3;

 

EDIT: made it an inner join.... you don't need a left join here.