Extracting a set record ids from a translations table using a subquery.
Then need to feed this set of ids to several WHERE clauses of another query in order to extract the record from a specific table (product\_listings) via a series of joins.

Table join structure

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

```

The set of ids returned by the subquery can be for any of the 4 tables above.

Subquery returning the sets of ids

```
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);

```

Main query here using the ids in WHERE clauses

```
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);    <---|

```

Both queries works perfectly independently. But cannot succesfully merge them into one.

Only dirty way I found is to repeat the subquery at each WHERE clause. Tried it and it works, but doubtfully the most effective and optimized way to do it.
Tried using variable, but only one value can be stored - unfortunately not a viable option.

Spent countless hours ressearching on how to avoid repeating a subquery and been rewriting those in many ways, but still can't get it to work.

Any suggestion on how to integrate the subquery elegantly and efficiently?

Currently working with Mysql Ver 14.14 Distrib 5.7.37, for Linux (x86\_64)

UPDATE 2022/04/16: Adding sample data of translations table and expected results of both queries

Sample of the translations table with those 2 ids

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

```

Here is the subquery response

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

```

And a the main query response (final expected results) using the set of hardcoded ids. I modified the select clause to return specific columns to make the table readable instead of the '\*'.
First 2 columns are the located set of ids in the products and product\_brands table and 2 other one are from the corresponding product\_listings record extracted via the joins.

```
+------------+----------+--------------+-----------------+
| 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          |
+------------+----------+--------------+-----------------+

```

As you can see, the ids from the subquery are matching different column. In this case 5951765 is the products.ch\_id and the 252242 is the product\_brands.ch\_id.

Below is a visual representation of what I'm trying to achieve considering the current (1):(n) relations of the tables
[![Inout/output](https://i.stack.imgur.com/iZka4.png)](https://i.stack.imgur.com/iZka4.png)