hello,
Just wondering if you got time to look into this one
On Wed, Jun 1, 2022 at 12:45 PM Surinder Mehra <[email protected]> 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
>
