hello, Just wondering if you got time to look into this one On Wed, Jun 1, 2022 at 12:45 PM Surinder Mehra <redni...@gmail.com> wrote:
> Hi, > I have the following sample code to demo issue in SQL joins. I have > created an affinity key and value as shown below and added some sample data > to it. When I try LEFT self join on this table it always gives me common > rows irrespective of LEFT or RIGHT JOIN > Could you please help me find what am I doing wrong here. > > cache Key : > > public class OrderAffinityKey { > Integer id; > @AffinityKeyMapped > Integer customerId; > } > > > cache value: > > public class Order implements Serializable { > @QuerySqlField > Integer id; > > @AffinityKeyMapped > @QuerySqlField Integer customerId; > @QuerySqlField String product; > } > > > Table C: (select customerID, product FROM "orderCache"."ORDER" WHERE > CUSTOMERID IN ( 1, 2)) > > 1 keyboard > 2 Laptop > > > Table O: (select customerID, product FROM "orderCache"."ORDER" WHERE > CUSTOMERID IN ( 3, 2)) > > 2 laptop > 3 mouse > > > > JOIN: > > Query : > select DISTINCT C.customerID, C.product, O.customerID > FROM > (select customerID, product FROM "orderCache"."ORDER" WHERE CUSTOMERID IN > ( 1, 2)) C > LEFT JOIN > (select customerID, product FROM "orderCache"."ORDER" WHERE CUSTOMERID IN > ( 3, 2)) O > ON > C.customerId = O.customerId > > > Output: > > 2 laptop 2 > 3 mouse 3 > > Expected output: > > 1 keyboard null > 2 laptop 2 > 3 mouse 3 >