mysql query to three tables, want to use JOIN instead subquery

Question

I want to use join instead subquery to find the trade id not exist on trade_log filtered by ip and current date for mysql syntax below.

SELECT plug.id as a,  
       plug.url as b,  
       trade.id as c  
  FROM plug, trade  
 WHERE trade.id = plug.trade_id  
   AND trade.id NOT IN (SELECT trade_log.trade_id 
                          FROM trade_log 
                         WHERE trade_log.ip = '".$ip."' 
                           AND trade_log.log_date = CURDATE())  
   AND trade.status = 1     
   AND plug.status = 1  
ORDER BY plug.weight DESC  
   LIMIT 1

Please help me...

Answer 1

Use:

   SELECT p.id as a,
          p.url as b,
          t.id as c
     FROM PLUG p
     JOIN TRADE t ON t.id = p.trade_id
                 AND t.status = 1
LEFT JOIN TRADE_LOG tl ON tl.trade_id = t.id
                      AND tl.ip = mysql_real_escape_string($ip)
                      AND tl.log_date = CURDATE()
    WHERE p.status = 1
      AND tl.ip IS NULL
 ORDER BY p.weight DESC
    LIMIT 1

Answer 2

Same as OMG Ponies:

SELECT p.id as a,
p.url as b,
t.id as c
FROM plug p
INNER JOIN trade t ON (t.id = p.trade_id AND t.status = 1)
LEFT JOIN trade_log l ON (l.trade_id = t.id AND l.ip = '".$ip."' AND l.log_date = CURDATE())
WHERE p.status = 1 AND l.trade_id IS NULL
ORDER BY p.weight DESC
LIMIT 1