Subselect in the context of a LEFT JOIN

[Former Member]

I am trying to execute the following query:

 SELECT *
 FROM {
     Product as p
  
     LEFT JOIN PriceRow AS prWeb on 
         {prWeb:product} = {p:PK} AND 
         {prWeb:ug} IN ({{ SELECT {thpg:pk} FROM {UserPriceGroup as thpg} WHERE {thpg:code} = 'web_price' }})
 }

It seams that the statement ({{ SELECT {thpg:pk} FROM {UserPriceGroup as thpg} WHERE {thpg:code} = 'web_price' }}) is not working in the context of a LEFT JOIN. Is this a restriction in hybris or it is an error in the syntax

I need this query in order to build a custom report, so executing a query before the join seems to not be an option in this case

[arvind-kumar_avinash]

I am not sure if the following query will fulfil your requirement:

Original answer:

 SELECT * FROM
   {
      Product as p LEFT JOIN PriceRow AS prWeb
         ON {prWeb:product} = {p:PK}
   } 
   WHERE {prWeb:ug} IN ({{ SELECT {thpg:pk} FROM {UserPriceGroup as thpg} WHERE {thpg:code} = 'web_price' }})

New answer based on clarification from :

 SELECT temp.PK FROM
 (
    {{
       SELECT {pk} FROM 
       {
         Product as p LEFT JOIN PriceRow AS prWeb on 
          {prWeb:product} = {p:PK}
       }
    }}
    UNION ALL
    {{
       SELECT {thpg:pk} FROM {UserPriceGroup as thpg} WHERE {thpg:code} = 'web_price'
    }}
 ) temp

[Former Member]

this query is not what I need, this query will filter out all the results that are not 'web_price' and I do not want that to happen. What I basically want is to show all the products and an additional column that has value only if the price is of type usergroup.

[arvind-kumar_avinash]

Hi - I have updated my answer based on your clarification. Please test it and let me know.

Wish you a Happy New Year!

Best regards.

[Former Member]

That would work in the case I have to Join just on a pricerow column, but if I have to extract multiple price columns for different price groups then the UNION will not work. Example of query (not working but just to give you an idea) with multiple pricerow per ug columns:

  SELECT {p:code}, {prWeb:price}, {prWeb1:price}
  FROM {
      Product as p
   
      LEFT JOIN PriceRow AS prWeb on 
          {prWeb:product} = {p:PK} AND 
          {prWeb:ug} IN ({{ SELECT {thpg:pk} FROM {UserPriceGroup as thpg} WHERE {thpg:code} = 'web_price' }})
 
       LEFT JOIN PriceRow AS prWeb1 on 
          {prWeb1:product} = {p:PK} AND 
          {prWeb1:ug} IN ({{ SELECT {thpg:pk} FROM {UserPriceGroup as thpg} WHERE {thpg:code} = 'web_price1' }})
  }

I think the only way to achieve what I want, is to be able to execute a subquery in the LEFT JOIN statement, this is possible to do in standard SQL but it seems to not be possible with a flexible search.

A possible solultion for the moment would be hardcoding the pks in the query or passing the usergroups as parameters in the report.