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.
CodePudding user response：
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