在没有子查询重复的情况下在各种 WHERE 子句中重用 MySQL 子查询 - 已解决

使用子查询从翻译表中提取一组记录 ID。然后需要将这组 id 提供给另一个查询的几个 WHERE 子句，以便通过一系列连接从特定表 (product_listings) 中提取记录。

找到解决方案！见这里。

表连接结构

product_brands(1) <-> (n)products(1) <-> (n)product_categories(1) <-> (n)product_listings 

子查询返回的 id 集可以是上述 4 个表中的任何一个。

返回一组 id 的子查询

select
  record_id
from
  translations
where
  translations.locale = 'en_CA'
  and (
    translations.table = 'product_listings'
    or translations.table = 'product_categories'
    or translations.table = 'products'
    or translations.table = 'product_brands'
  )
  and MATCH (translations.translation) AGAINST ('+jack*' IN BOOLEAN MODE);

此处使用 WHERE 子句中的 id 进行主查询

select
  product_listings.*
from
  product_listings
  left join product_categories on product_categories.ch_id = product_listings.ch_vintage_id
  left join products on products.ch_id = product_categories.ch_product_id
  left join product_brands on product_brands.ch_id = products.ch_brand_id
where
    product_listings.ch_id in (5951765, 252242)      <---| Replace these fixed ids
    or product_categories.ch_id in (5951765, 252242) <---| with the "record_id" set
    or products.ch_id in (5951765, 252242)           <---| returned by the subquery
    or product_brands.ch_id in (5951765, 252242);    <---|

这两个查询完全独立地工作。但不能成功地将它们合并为一个。

我发现唯一肮脏的方法是在每个 WHERE 子句中重复子查询。尝试过它并且它有效，但无疑是最有效和优化的方法。尝试使用变量，但只能存储一个值 - 不幸的是，这不是一个可行的选择。

花了无数个小时研究如何避免重复子查询并以多种方式重写它们，但仍然无法使其正常工作。

关于如何优雅高效地集成子查询的任何建议？

目前正在使用适用于 Linux (x86_64) 的 Mysql Ver 14.14 Distrib 5.7.37

更新 2022/04/16：添加翻译表的示例数据和两个查询的预期结果

具有这 2 个 ID 的翻译表示例

+-----------+----------------+--------+-------------------------------+
| record_id | table          | locale | translation                   |
+-----------+----------------+--------+-------------------------------+
|   5951765 | products       | en_CA  | Jack Daniel's                 |
|    252242 | product_brands | en_CA  | Dixon's & Jack Daniel's       |
+-----------+----------------+--------+-------------------------------+

这是子查询响应

+-----------+
| record_id |
+-----------+
|   5951765 |
|    252242 |
+-----------+

以及使用一组硬编码 id 的主要查询响应（最终预期结果）。我修改了 select 子句以返回特定列，以使表可读而不是“*”。前 2 列是 products 和 product_brands 表中定位的 id 集，另外 2 列来自通过连接提取的相应 product_listings 记录。

+------------+----------+--------------+-----------------+
| product_id | brand_id | listing_cspc | listing_format |
+------------+----------+--------------+-----------------+
|    5951765 |  5936861 | 798248       | 6x750           |
|    5951765 |  5936861 | 545186       | 6x750           |
|    5951956 |   252242 | 400669       | 12x750          |
|    5951955 |   252242 | 400666       | 12x750          |
|    5951701 |   252242 | 437924       | 12x750          |
|    5951337 |   252242 | 20244        | 6x750           |
|    5950782 |   252242 | 65166        | 12x750          |
|    5950528 |   252242 | 104941       | 12x750          |
|    5949763 |   252242 | 13990091     | 12x750          |
|    5949750 |   252242 | 614064       | 12x750          |
...
|    1729121 |   252242 | 280248       | 12x750          |
|    1729121 |   252242 | 36414        | 12x750          |
+------------+----------+--------------+-----------------+

如您所见，子查询中的 id 匹配不同的列。在这种情况下，5951765 是 products.ch_id，而 252242 是 product_brands.ch_id。

下面是考虑当前 (1):(n) 表关系的我试图实现的视觉表示