How to display both true and false rows in MySQL with IF() condition-CodePudding

Order Table

 --------- ------------ ---------------- ---------- ------------ 
| OrderID | CustomerID | DateOfPurchase | Discount | DueDate    |
 --------- ------------ ---------------- ---------- ------------ 
|      82 |          7 | 2022-04-17     | 0        | 2022-05-17 |
|      83 |         91 | 2022-04-17     | 0        | 2022-05-17 |
|      84 |          8 | 2022-04-17     | 0        | 2022-05-17 |
|      85 |         91 | 2022-04-17     | 0        | 2022-05-17 |
|      86 |          7 | 2022-04-17     | 0        | 2022-05-17 |
|      87 |         91 | 2022-04-18     | 0        | 2022-05-18 |
|     109 |          7 | 2022-04-25     | 0        | 2022-05-25 |
 --------- ------------ ---------------- ---------- ------------

Customer table

 ------------ ---------- ------- ------- ------------- ----------------------------- ---------- -------------- 
| CustomerID | Fname    | Mname | Lname | Contact_no  | Address                     | Valid_id | Credit_Limit |
 ------------ ---------- ------- ------- ------------- ----------------------------- ---------- -------------- 
|          7 | John     | Dale  | Doe   | 09123654789 | Asan Sur, Sison, Pangasinan | NULL     |     5000.000 |
|          8 | Jane     | Dale  | Doe   | 09987654123 | Asan Sur, Sison, Pangasinan | NULL     |     1500.000 |
|         91 | Kurdapya | Buang | Selos | 09741258963 | Paldit, Sison, Pangasinan   | NULL     |     5000.000 |
 ------------ ---------- ------- ------- ------------- ----------------------------- ---------- --------------

Payment table

 ----------- ------------ --------- ------------ ---------- 
| PaymentID | CustomerID | OrderID | PayDate    | Amount   |
 ----------- ------------ --------- ------------ ---------- 
|        20 |          7 |      82 | 2022-04-25 |  800.000 |
|        21 |         91 |      83 | 2022-04-17 | 2500.000 |
|        22 |         91 |      85 | 2022-04-17 |  200.000 |
|        23 |         95 |      88 | 2022-04-18 | 2122.000 |
|        24 |         96 |      90 | 2022-04-25 |  577.000 |
|        25 |         97 |     111 | 2022-04-25 |    0.000 |
|        26 |         98 |     114 | 2022-04-25 |  166.000 |
|        27 |         99 |     115 | 2022-04-25 | 1740.000 |
 ----------- ------------ --------- ------------ ----------

I want to know which are the OrderID of Customer Kurdapya (OrderID=91) that are paid and unpaid

this is the Query I have tried so far

Attempt 1:

select if(py.OrderID=r.OrderID, 'paid','unpaid') as remarks, r.OrderID, r.CustomerID
from orders r,
     payment py
where py.OrderID = r.OrderID and r.CustomerID = 91
GROUP by r.OrderID;

Result for attempt 1:

 --------- --------- ------------ 
| remarks | OrderID | CustomerID |
 --------- --------- ------------ 
| paid    |      83 |         91 |
| paid    |      85 |         91 |
 --------- --------- ------------

Attempt 2:

select if(py.OrderID=r.OrderID and py.OrderID=py.Amount!='null', 'paid','unpaid') as remarks, r.OrderID, r.CustomerID
from orders r,
     payment py
where r.CustomerID = 91
GROUP by r.OrderID;

Result of Attempt 2:

 --------- --------- ------------ 
| remarks | OrderID | CustomerID |
 --------- --------- ------------ 
| unpaid  |      83 |         91 |
| unpaid  |      85 |         91 |
| unpaid  |      87 |         91 |
 --------- --------- ------------

My DESIRED RESULT IS THIS:

     --------- --------- ------------ 
    | remarks | OrderID | CustomerID |
     --------- --------- ------------ 
    | unpaid  |      83 |         91 |
    | unpaid  |      85 |         91 |
    | paid    |      87 |         91 |
     --------- --------- ------------

CodePudding user response：

You can try

select  CASE
    WHEN P.Amount > 0 then 'paid' else 'unpaid' end as remarks,
        O.orderID, O.CustomerID
from Customer C inner join OrderTable O on C.CustomerID  = O.CustomerID 
inner join Payment P on P.OrderID = O.OrderID
where O.CustomerID = 91

CodePudding user response：

SELECT
  IF(py.OrderID IS NULL, 'unpaid', 'paid') AS remarks,
  r.OrderID,
  r.CustomerID
FROM orders AS r
LEFT OUTER JOIN payment AS py USING (OrderID)
WHERE r.CustomerID = 91

However, since the order table doesn't have an amount, I don't know if the amount paid is sufficient to pay for the full order.

CodePudding user response：

Your second query didn't even compile. Try this

select if(py.Amount is not null, 'paid','unpaid') as remarks, r.OrderID, r.CustomerID
from orders r
left outer join payment py on r.OrderID=py.OrderID
where r.CustomerID = 91;

Points to note:

I have used the modern, explicit JOIN syntax - see the comment from @jarlh
I have used LEFT OUTER join
I have removed the quotation marks from 'null' - NULL is a specific value whereas 'null' is a string
The GROUP BY is unecessary