How to create flexible search query with UNION & IN operator?

Question

After executing below query i am getting UNION of all products from both catalogs "catalogA" & "catalogB" Query :

SELECT uniontable.PK, uniontable.creationtime AS TIMECREATED FROM
    (
       {{
          SELECT {p:mode},{p:brandno},{creationtime} AS creationtime FROM {Product AS p JOIN catalogversion AS cv ON
 {p:catalogversion}={cv:pk} JOIN catalog AS cat ON {p:catalog}={cat:pk}}
          WHERE {p.code} = '145' AND {cv:version}='Staged' AND {cat:id}='catalogA'
       }}
       UNION ALL
       {{
          SELECT {p:mode},{p:brandno},{creationtime} AS creationtime FROM {Product AS p JOIN catalogversion AS cv ON
 {p:catalogversion}={cv:pk} JOIN catalog AS cat ON {p:catalog}={cat:pk}}
          WHERE {p.code} = '123' AND {cv:version}='Staged' AND {cat:id}='catalogB'
       }}
    ) uniontable ORDERR BY TIMECREATED

I need products Only from CatalogA from UNION query so i did a subquery as below but it throws error. Any help would be appreciated?

Query 2 :

SELECT uniontable.PK, uniontable.creationtime AS TIMECREATED FROM {Product AS p JOIN catalogversion AS cv ON
 {p:catalogversion}={cv:pk} JOIN catalog AS cat ON {p:catalog}={cat:pk}}
          WHERE {cv:version}='Staged' AND {cat:id}='catalogA' IN 
    (
       {{
          SELECT {p:mode},{p:brandno},{creationtime} AS creationtime FROM {Product AS p JOIN catalogversion AS cv ON
 {p:catalogversion}={cv:pk} JOIN catalog AS cat ON {p:catalog}={cat:pk}}
          WHERE {p.code} = '145' AND {cv:version}='Staged' AND {cat:id}='catalogA'
       }}
       UNION ALL
       {{
          SELECT {p:mode},{p:brandno},{creationtime} AS creationtime FROM {Product AS p JOIN catalogversion AS cv ON
 {p:catalogversion}={cv:pk} JOIN catalog AS cat ON {p:catalog}={cat:pk}}
          WHERE {p.code} = '123' AND {cv:version}='Staged' AND {cat:id}='catalogB'
       }}
    ) uniontable ORDERR BY TIMECREATED

Just for my understanding, why don't you simply fetch the data by querying the product table directly instead of UNION. Anyway, let me answer based on the scenario you have mentioned. — HybrisHelp, Jul 28 '19 at 06:06

HybrisHelp · Answer 1 · 2019-07-28T06:05:53.227

You just need to filter the temporary table with WHERE uniontable.catalog = 'catalogA'

Like

SELECT * FROM
(
   {{
      SELECT {p:pk},{p:mode},{p:brandno},{creationtime} AS creationtime, {cat:id} AS catalog 
      FROM {    Product AS p 
                JOIN catalogversion AS cv ON {p:catalogversion}={cv:pk} 
                JOIN catalog AS cat ON {p:catalog}={cat:pk}
            }
      WHERE {p.code} = '145' AND {cv:version}='Staged' AND {cat:id}='catalogA'
   }}
   UNION ALL
   {{
      SELECT {p:pk},{p:mode},{p:brandno},{creationtime} AS creationtime, {cat:id} AS catalog  
      FROM {    Product AS p 
                JOIN catalogversion AS cv ON {p:catalogversion}={cv:pk} 
                JOIN catalog AS cat ON {p:catalog}={cat:pk}
            }
      WHERE {p.code} = '123' AND {cv:version}='Staged' AND {cat:id}='catalogB'
   }}
) as uniontable WHERE uniontable.catalog = 'catalogA' ORDERR BY uniontable.creationtime

How to create flexible search query with UNION & IN operator?

1 Answers1