php - 如何在单个查询中调整多个 'where' 子句？

Question

我有这个查询，它给了我以前的结果 id，但它包含多个 where 子句。

SELECT DISTINCT Contacts.cm_id 
    FROM Contacts 
    LEFT JOIN Employee ON (Contacts.cm_id = Employee.ei_contact_id) AND (Employee.ei_primary_employer = "Y") 

WHERE ( CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) LIKE '%Recee Dawn%' 
    AND CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) NOT LIKE 'NULL' ) 

WHERE Contacts.cm_id < (
    SELECT DISTINCT Contacts.cm_id 
        FROM Contacts 
        LEFT JOIN Employee ON (Contacts.cm_id = Employee.ei_contact_id) AND (Employee.ei_primary_employer = "Y") 
        WHERE ( CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) LIKE '%Recee Dawn%' AND CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) NOT LIKE 'NULL' ) 
        WHERE Contacts.cm_id='77')

ORDER BY Contacts.cm_id DESC LIMIT 1

它给我这个错误

'WHERE Contacts.cm_id='313')) ORDER BY Contact_Master.cm_id DESC LIMIT 1' at line 10

如何调整此查询以避免查询中断。请帮忙

Answer 1

您的查询语法错误，LEFT JOIN ... ON ... 之后的 AND 应该在 WHERE 语句中，并将 WHERE caluses 与 AND 而不是 WHERE 每次组合，通常你只有一个 WHERE 用于每个 选择。

尝试这样的事情:

SELECT DISTINCT Contacts.cm_id 
FROM Contacts LEFT JOIN Employee 
     ON (Contacts.cm_id = Employee.ei_contact_id) 
WHERE (Employee.ei_primary_employer = "Y") 
  AND ( CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) LIKE '%Recee Dawn%' 
  AND CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) NOT LIKE 'NULL' ) 
  AND Contacts.cm_id < 
     (SELECT DISTINCT Contacts.cm_id 
      FROM Contacts LEFT JOIN Employee 
           ON (Contacts.cm_id = Employee.ei_contact_id)  
      WHERE (Employee.ei_primary_employer = "Y") 
        AND (CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) LIKE '%Recee Dawn%' 
        AND CONCAT( Contacts.cm_fname," ", Contacts.cm_lname ) NOT LIKE 'NULL' ) 
        AND Contacts.cm_id='77') ORDER BY Contacts.cm_id DESC LIMIT 1)