mysql query - select distinct value where it has a specific values , group by

Question

I currently have

SELECT * FROM bidders 
WHERE status='0' 
AND email IS NOT NULL 
GROUP BY `bid_user` 
ORDER BY 'bid_price' DESC

The problem that I have is that "bid_user" may exist in different rows with different status ( status=1 or status=0). I would like to know if it's possible to select only the * rows where status=0 and where bid_user doesn't doesn't exist with status=1 along with the above conditions(AND email IS NOT NULL GROUP BY bid_user ORDER BY 'bid_price' DESC). I can work around this issue using PHP + 2 mysql queries but it would be much way better to have the exact query in mysql .

Answer 1

You can do that by replacing "status='0'" condition by "status='0' OR (status='1' AND bid_user IS NULL)"

Answer 2

As you want * rows with status='0' AND email IS NOT NULL don't use GROUP BY

SELECT * FROM bidders 
WHERE status='0' 
AND email IS NOT NULL 
ORDER BY 'bid_price' DESC

EDITED (As per comment of @Michael )

Data

id  bid_price  bid_user status
1   100        test      0
2   200        test      1
3   300        test2     0

O/P Required

id  bid_price  bid_user status
3   300        test2      0

SQL Query

   SELECT * FROM bidders 
     WHERE bid_user NOT IN (select DISTINCT `bid_user` FROM `bidders` where status='1') 
       AND email IS NOT NULL 
     ORDER BY 'bid_price' DESC

Answer 3

An alternative which is likely to perform better assuming that you have indexes on bid_user and status:

SELECT 
  b.* 
FROM 
  bidders b
WHERE 
  status='0' 
  AND email IS NOT NULL 
  AND not exists (
    select 1 from bidders bx where bx.status='1' and bx.bid_user = b.bid_user
  )
ORDER BY 'bid_price' DESC