Member Avatar for MDanz

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";
Edited by MDanz
  • 2 Contributors
  • 1 Reply
  • 125 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by |-|x
Member Avatar for |-|x

Your first query does give me rows 4 and 7

The second however returned no results.

Edited by |-|x
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.