I have two table similar below

If we have shopping cart table as carts is like below

id     date     title
---------------------
1      2016       a
2      2015       b
3      2018       c
4      2019       dd

And product table is some thing like below

id     cart_id    status
--------------------------
1         2        paid
2         2        paid
3         1        paid
4         1        unpaid
5         1        paid
6         3        paid
7         3        paid

Expected output (only carts in which All product has been paid) is:

cart_id    status
-------------------
   3        paid
   2        paid

3 Answers

0
mkRabbani On

Try This adjusted code-

SELECT carts.id,'Paid' Status
FROM carts
INNER JOIN product on carts.id = product.cart_id
GROUP BY carts.id
HAVING COUNT(Status) = SUM(CASE WHEN status = 'Paid' THEN 1 ELSE 0 END)
0
Gordon Linoff On

This seems the simplest logic:

select p.cart_id, p.status
from product p
where p.status = 'paid';
0
forpas On

You can group by cart_id and with a HAVING clause apply the condition that there is no 'unpaid' value in the column status:

select cart_id, max(status) status
from product
group by cart_id
having sum(status = 'unpaid') = 0

I assume that in the column status the only possible values are 'paid' and 'unpaid', so instead of max(status) status you can use 'paid' status as the 2nd column.
Or with NOT EXISTS:

select distinct p.cart_id, p.status
from product p
where not exists (
  select 1 from product
  where cart_id = p.cart_id and status = 'unpaid'
)

See the demo.
Results:

| cart_id | status |
| ------- | ------ |
| 2       | paid   |
| 3       | paid   |