cancel
Showing results for 
Search instead for 
Did you mean: 

Subselect in the context of a LEFT JOIN

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member620692
Active Contributor
0 Kudos

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
0 Kudos

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.

former_member620692
Active Contributor
0 Kudos

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
0 Kudos

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.