php - 如何修复使用搜索词返回大量数据的查询

Question

我正在运行一个查询来检索我的数据的所有客户。由于新法律，并非所有客户都存储了地址，因此我有一些空值。如果我运行“主”查询检索所有离开加入另一个表的客户，它会很好地工作。当我尝试添加搜索词时，它仍然给我整个集合，而不是根据搜索词过滤它。我做错了什么？

我尝试在没有左连接的情况下重写查询，只是一个 WHERE c.customers_id = a.customers_id 但是因为 a.customers_id 并不总是被填充，所以我得到的客户比我少得多

select 
c.customers_id, c.customers_firstname, c.customers_lastname, c.customers_email_address, c.customers_account_validated, c.customers_payment_period, a.entry_country_id, a.entry_company 
from customers c 
left join address_book a on c.customers_id = a.customers_id 
and c.customers_default_address_id = a.address_book_id 
and (c.customers_lastname like '%adolfs%' 
or c.customers_firstname like '%adolfs%' 
or c.customers_email_address like '%adolfs%' 
or a.entry_company like '%adolfs%' or c.customers_id = 'adolfs') 
order by c.customers_id DESC

我希望所有客户在电子邮件地址中使用“adolfs”作为名字、姓氏、公司名称的结果

Answer 1

and (c.customers_lastname like '%adolfs%' 
or c.customers_firstname like '%adolfs%' 
or c.customers_email_address like '%adolfs%' 
or a.entry_company like '%adolfs%' or c.customers_id = 'adolfs') 

这些应该在 where 子句中而不是你的连接的一部分

select 
c.customers_id, c.customers_firstname, c.customers_lastname, 
c.customers_email_address, c.customers_account_validated, 
c.customers_payment_period, a.entry_country_id, a.entry_company 
from customers c 
left join address_book a on c.customers_id = a.customers_id 
and c.customers_default_address_id = a.address_book_id 
where c.customers_lastname like '%adolfs%' 
or c.customers_firstname like '%adolfs%' 
or c.customers_email_address like '%adolfs%' 
or a.entry_company like '%adolfs%' 
or c.customers_id = 'adolfs') 
group by c.customers_id 
order by c.customers_id DESC

这应该行得通