Mysql SELECT ... WHERE IN(从多个表中进行多个子选择)

Question

Mysql需要从一张表中获取数据，其中ids与另一张表中的列值相同。但从另一个表中必须仅获取列值，这些 id 与其他两个表中的列值相同 (IN)。

表格如下:

带有列的表名称sales_invoices(下面我没有显示所有列，有一些列是问题所必需的)。
IdInvoices | 日期

表名称 purchase_invoices 包含列
IdInvoicesP | 日期

表名称items和列
IdI | IdGoodsServices | IdInvoices

表名称 goods_services 以及列
IdGs(唯一值)

以下是表格之间的链接
IdGs = IdGoodsServices
IdInvoices (items) = IdInvoices (sales_invoices) = IdInvoicesP

尝试了多种代码变体。在所有情况下都会出现错误。下面是最后使用的代码。

SELECT count(DISTINCT `IdGs`) FROM `goods_services` WHERE `IdGs` IN 

  ( SELECT `IdGoodsServices` FROM `items` WHERE `IdInvoices` IN 

    ( SELECT `IdInvoices`, `IdInvoicesP` FROM 
    `sales_invoices`, `purchase_invoices` WHERE `Date` <= ? ) 

  )  

想要计算在特定日期之前开具(书面)的销售和采购发票中包含的 IdG(产品或商品/服务的代码)。

goods_services 和 sales_invoices/purchase_invoices 之间没有直接链接。表 items 链接到 goods_services 和 (sales_invoices/purchase_invoices)

使用上面的代码会出现错误
SQLSTATE[23000]:违反完整性约束:1052 where 子句中的列“日期”不明确

尝试仅从一个表中进行子子选择，得到一些结果并且没有错误。
这是查询

SELECT count(DISTINCT `IdGs`) FROM `goods_services` WHERE `IdGs` IN 

  ( SELECT `IdGoodsServices` FROM `30u2s5r_items` WHERE `IdInvoices` IN 

    ( SELECT `IdInvoicesP` FROM 
    `30u2s5r_purchase_invoices` WHERE `Date` <= ? ) 

  )  

下面是工作代码(正如我到目前为止所测试的那样)。

SELECT count(DISTINCT `IdGs`) FROM `goods_services` WHERE `IdGs` IN 

 (SELECT `IdGoodsServices` FROM `items` WHERE 

 `IdInvoices` IN 

  (SELECT DISTINCT `IdInvoices` FROM `sales_invoices` 
  WHERE `sales_invoices`.`Date` <= ? ) 

   OR 

  `IdInvoices` IN 
  ( SELECT DISTINCT `IdInvoicesP` FROM `purchase_invoices` 
  WHERE `purchase_invoices`.`Date` <= ? ) 

)

Answer 1

使用purchase_invoices.Date或sales_invoices.Date