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

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

那应该工作