Hybris Flexible search union query to fetch products

Question

   code    Attribute1(String)

    A         C
    B         D
    C         Empty
    D         Empty 

how to get the pk's of all A,B,C,D

Note: Using the string value C,D I want to fetch pk of product C,D along with A,B using Flexible search query

Details :

I Have list a of product's.

Inside each of these products there is an attribute called "X" which contains product ID code of type string.

Note : "Product ID Code" means "Product ID" of another product inside the list of products.

Now i want to get pk's of products based upon the Product ID Codes?

Answer 1

I don't understand it fully, but you can try something like this

select {p1.pk},{p2.pk} from {product as p1},{product as p2} WHERE {p1.Attribute1} = {p2.code}

you can add filter to it

 AND {p1.Attribute1} in ('C','D')

---

Using UNION

SELECT uniontable.PK FROM
(
   {{
      SELECT {p1:PK} AS PK FROM {Product AS p1},{Product AS p2} 
      WHERE {p1.code} = {p2.Attribute1}
   }}
   UNION ALL
   {{
      SELECT {p:PK} AS PK FROM {Product AS p}
      WHERE {p1.Attribute1} is not empty
   }}
) uniontable

With filter

SELECT uniontable.PK FROM
(
   {{
      SELECT {p1:PK} AS PK FROM {Product AS p1},{Product AS p2} 
      WHERE {p1.code} = {p2.Attribute1} AND {p2.Attribute1} in ('C','D')
   }}
   UNION ALL
   {{
      SELECT {p:PK} AS PK FROM {Product AS p}
      WHERE {p.Attribute1} in ('C','D')
   }}
) uniontable