Case and Join statement giving duplicate rows, how to fix it

Question

TABLE 1

select * from product;

IDPK (PK)   PNAME
----------- --------------------
          1 AAA
          2 BBB
          3 CCC

  3 record(s) selected.

++++++++++++++++++++++++++++++++++++++++

TABLE 2

select * from productcost;

IDPK (FK)   QUANTITY                   PRICE
----------- -------------------- -----------
          1 100                          100
          1 250                          250
          1 500                          500
          2 100                          100
          2 250                          250
          2 500                          500
          3 100                          100
          3 250                          250
          3 500                          500

  9 record(s) selected.

Quantity in Grams… IDPK FK references IDPK of Product table

++++++++++++++++++++++++++++++++++++++++

TABLE 3

select * from cart;

IDPK (FK)   QUANTITY             ITEMCOUNT
----------- -------------------- -----------
          1 100                            5
          1 250                            5

  2 record(s) selected.

Quantity in Grams… IDPK FK references IDPK of Product table

++++++++++++++++++++++++++++++++++++++++

At first, joining first 2 tables, I can get below result – easy one.

select a.idpk, a.pname, b.quantity, b.price from product a inner join productcost b on a.idpk = b.idpk;

IDPK        PNAME                QUANTITY                   PRICE           
----------- -------------------- -------------------- -----------
          1 AAA                  100                          100
          1 AAA                  250                          250
          1 AAA                  500                          500
          2 BBB                  100                          100
          2 BBB                  250                          250
          2 BBB                  500                          500
          3 CCC                  100                          100
          3 CCC                  250                          250
          3 CCC                  500                          500

  9 record(s) selected.

++++++++++++++++++++++++++++++++++++++++

Now desired output is:

IDPK        PNAME                QUANTITY             PRICE             ITEMCOUNT
----------- -------------------- -------------------- -----------       -----------
          1 AAA                  100                          100       5
          1 AAA                  250                          250       5
          1 AAA                  500                          500       0
          2 BBB                  100                          100       0
          2 BBB                  250                          250       0
          2 BBB                  500                          500       0
          3 CCC                  100                          100       0
          3 CCC                  250                          250       0
          3 CCC                  500                          500       0

  9 record(s) selected.

But my below query giving wrong results, 12 records coming instead of 9. How to fix my query.

SELECT X.idpk,
       X.pname,
       X.quantity,
       X.price,
       CASE
         WHEN X.quantity = Y.quantity THEN Y.itemcount
         ELSE 0
       END AS itemcount
FROM (SELECT a.idpk,
             a.pname,
             b.quantity,
             b.price
      FROM product a
        INNER JOIN productcost b ON a.idpk = b.idpk) X
  LEFT OUTER JOIN (SELECT * FROM cart) Y ON X.idpk = Y.idpk;

IDPK        PNAME                QUANTITY             PRICE       ITEMCOUNT
----------- -------------------- -------------------- ----------- -----------
          1 AAA                  100                          100           5 -- first time
          1 AAA                  500                          500           0
          1 AAA                  250                          250           0
          1 AAA                  100                          100           0 -- second time row is coming, one with correct itemcount 5, and one for 0 also which shouldn’t
          1 AAA                  500                          500           0
          1 AAA                  250                          250           5
          3 CCC                  100                          100           0
          3 CCC                  250                          250           0
          3 CCC                  500                          500           0
          2 BBB                  100                          100           0
          2 BBB                  250                          250           0
          2 BBB                  500                          500           0

  12 record(s) selected.

Answer 1

I guess, you need to look for cart records only when quantity matches the productcost quantity. Also, you do not need sub-queries. It's simple:

Select a.idpk
      ,a.pname
      ,b.quantity
      ,b.price
      ,case when b.quantity = Y.quantity then Y.itemcount else 0 end as itemcount 
from product a 
inner join productcost b 
    on a.idpk = b.idpk
left outer join cart Y 
    on a.idpk = Y.idpk
    AND b.quantity = Y.quantity

Also, this line:

case when b.quantity = Y.quantity then Y.itemcount else 0 end as itemcount 

can be further simplified using ISNULL or COALESCE (depending on your RDMS) like:

ISNULL(Y.itemcount, 0) AS itemcount