SQL IF and LEFT JOIN cause database to malfunction

I just encoutered a problem I never had before. When I try my SQL statement in phpmyadmin all is fine. However, once I use it in my c application with MySQL, it only works every other start. Also when I run it once, it blocks the entire table and the table then does not return any results, not even in phpmyadmin.

The SQL is as follows:

    SELECT watchedItems.aid, IF((watchedItems.maxBidPrice > 0.00), watchedItems.maxBidPrice, bidGroups.bidGroupPrice)   
AS maxBidPrice 
FROM watchedItems 
LEFT JOIN bidGroups ON bidGroups.bidGroupID = watche开发者_运维百科dItems.bidGroupID 
WHERE watchedItems.sniping = 1 
AND watchedItems.deleted = 0 
AND watchedItems.PID = 0 
AND watchedItems.processRunning = 0 
AND watchedItems.id = 1

Before everything is filtered out by WHERE, LEFT JOIN fills everything with NULL, could this somehow affect the table (NULL pointers)? Or is the IF statement misplaced? Or maybe something wrong with the LEFT JOIN? The strange thing is, if I adapt the code and only use:

  SELECT watchedItems.aid, IF((watchedItems.maxBidPrice > 0.00), watchedItems.maxBidPrice, bidGroups.bidGroupPrice)   
AS maxBidPrice 
FROM watchedItems 
LEFT JOIN bidGroups ON bidGroups.bidGroupID = watchedItems.bidGroupID 
WHERE 
watchedItems.id = 1

everything seems to work at least on the surface - I do not get the table blockages. Maybe I'm still missing something here.

---

Try this instead

SELECT watchedItems.aid, IF((bigGroups.bigGroupID IS NOT NULL AND watchedItems.maxBidPrice > 0.00), watchedItems.maxBidPrice, bidGroups.bidGroupPrice)   

the > 0.00 doesn't guarantee that there is data from bigGroups.

---

Make sure all columns in the where clause and maxBidPrice have indexes, or the request will be very costly. (There is a button for that in the phpMyAdmin table structure view besides the columns.)

It works in phpMyAdmin because it silently adds limit 50 to the sql code.

If maxBidPrice can be null, you can use COALESCE:

IF((COALESCE(watchedItems.maxBidPrice, 0.00) > 0.00),
    watchedItems.maxBidPrice, bidGroups.bidGroupPrice)

If maxBidPrice is always >0 if not NULL, use IFNULL:

IFNULL(watchedItems.maxBidPrice, bidGroups.bidGroupPrice)