How to do UNION in flexible query

Question

I want to merge catetegory_id and subCategory_id into one column. But I don't know how to do it in flexible query. Below code is what I want to do

SELECT * FROM 

(SELECT
    ccr.targetpk as pk
    FROM
    Categories cc
    JOIN Cat2CatRel ccr ON cc.pk = ccr.sourcepk
    WHERE cc.p_code = '0106') 

UNION  SELECT CATE.pk as pk FROM CATEGORIES cate WHERE CATE.p_code = '0106'

I need a flexible query which works same as above code.

Answer 1

it is possible to do unions in flexible search queries. Here is an example like your case:

SELECT uniontable.PK FROM
(
   {{
      SELECT {cc:PK} as PK, {cc:code} AS CODE FROM {Categories AS cc}
      WHERE {cc:p_code} LIKE '0106'
   }}
   UNION ALL
   {{
      SELECT {ccr:PK} as PK, {ccr:code} AS CODE FROM {Cat2CatRel AS ccr}
      WHERE {ccr:p_code} LIKE '0106'
   }}
) uniontable

Here you can find some more information about flex searches: Hybris Wiki