我可以将 like where 子句的结果连接到另一个表吗?

Question

我想使用带有通配符的 LIKE 对一个表"客户"进行搜索，并在另一个"收据"上使用这些结果加入不确定如何实现这一点，我正在使用访问数据库

即

SELECT customercode, firstname, lastname FROM customers WHERE customercode LIKE *searchterm AS RESULTS 
INNER JOIN receipts ON customers.customercode = receipts.customercode

我不确定如何构建此查询，在 WHERE 之前加入 2 似乎非常缓慢，因为有许多收据与客户匹配。因此，我想首先将集合剔除到匹配的客户，然后将其连接到匹配客户代码上的收据。

任何帮助表示赞赏。

SELECT customercode, firstname, lastname FROM customers WHERE customercode LIKE *searchterm AS RESULTS 
INNER JOIN receipts ON customers.customercode = receipts.customercode

我目前正在使用

SELECT Docketnumber, date, total, tblcustomer.customercode, firstname, lastname, businessname FROM tblreceipts INNER JOIN tblcustomer on tblreceipts.customercode = tblcustomer.customercode WHERE firstname like 'c%'

Answer 1

您可以筛选客户，然后加入收据，如下所示：

SELECT c.customercode, c.firstname, c.lastname 
FROM (
SELECT * FROM customers 
WHERE customercode LIKE '*searchterm'
) AS c INNER JOIN receipts AS r 
ON c.customercode = r.customercode

Answer 2

您可以使用联接来关联表，并使用 where 条件筛选来自客户的结果

SELECT c.customercode
, c.firstname
, c.lastname 
FROM customers c
INNER JOIN receipts r ON c.customercode = r.customercode
WHERE c.customercode LIKE '%your_value%'  

Answer 3

如果您只想从customers表中获取信息，我建议您EXISTS：

SELECT c.customercode, c.firstname, c.lastname
FROM customers as c
WHERE c.customercode LIKE "*searchterm" AND
EXISTS (SELECT 1 
FROM receipts as r
WHERE c.customercode = r.customercode
);

非常重要：如果客户有多个收据，这不会返回多行。 如果您想要receipts表中的列，则可以使用联接，但您的问题并非如此。