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

Question

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 |  | NULL     |     5000.000 |
|          8 | Jane     | Dale  | Doe   | 09987654123 | | NULL     |     1500.000 |
|         91 | Kurdapya | Buang | Selos | 09741258963 |   | 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 |
    +---------+---------+------------+

Tip of today: Switch to modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh, Apr 26 '22 at 14:05
Do LEFT JOIN. Skip the GROUP BY. Use a case expression instead of if. — jarlh, Apr 26 '22 at 14:09

score 2 · Accepted Answer · answered Apr 26 '22 at 14:25

2

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.

answered Apr 26 '22 at 14:25

Bill Karwin

538,548
86
673
828

Good point about the payment matching - one for the OP to consider! – CHill60 Apr 26 '22 at 14:30
One might think a payment would not be accepted unless it's for the full amount of the order, but the customer might be paying for the order on layaway... – Bill Karwin Apr 26 '22 at 14:31
The "fun" scenario is of course where a single payment is intended to cover more than one order :-) – CHill60 Apr 26 '22 at 14:50
Been there, done that! :-) Let the customer may a single payment (as a courtesy), but the application code should break up the payment and record it in multiple rows, each referencing _one_ order. – Bill Karwin Apr 26 '22 at 14:55
may i ask why did you use the USE()? – kaponox Apr 28 '22 at 01:11
`USING(OrderId)` does the same thing as `ON r.OrderID = py.OrderID`. It's a shorthand that is permitted if the column is the same name in both tables, and the join condition is `=`. You may use either style of syntax. – Bill Karwin Apr 28 '22 at 01:20
so its kinda like the easier way thank you – kaponox Apr 28 '22 at 02:19
btw can i ask for your help again? This is my question https://stackoverflow.com/questions/72032088/how-to-display-the-paid-and-unpaid-amount-of-order-in-mysql – kaponox Apr 28 '22 at 02:20

score 1 · Answer 2 · answered Apr 26 '22 at 14:10

1

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

answered Apr 26 '22 at 14:10

JohanB

346
2
10

the result is same as my first attempt – kaponox Apr 26 '22 at 14:19
I retry it again but instead of using of inner join I use the left join as recommended on the comment section above. – kaponox Apr 26 '22 at 14:21
According to your sample data, the result 1 seems correct – JohanB Apr 26 '22 at 14:23
You omit order number 87 because you have incorrectly used an inner join – CHill60 Apr 26 '22 at 14:28

score 1 · Answer 3 · answered Apr 26 '22 at 14:25

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

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

3 Answers3