如何在SQL上创建if else语句？

Question

Hi Guys I'm having a Problem on how i could make a if else statement on SQL, It only displays the value of rent.rent_status = 1 on a Modal, but if rent.rent_status has no value the modal wont display, how can i make it display? Here is my code for your reference;

        $sql = "SELECT * FROM stall 
        LEFT JOIN tenant ON tenant.stall_id = stall.stall_id 
        LEFT JOIN rent ON rent.tenant_id = tenant.tenant_id
        WHERE rent.rent_status = 1 AND stall.stall_id = 1";

Answer 1

You are using LEFT JOIN and then undoing the outer join in the WHERE clause. You need to move the conditions to the appropriate ON clauses:

SELECT *
FROM stall s LEFT JOIN
     tenant t 
     ON t.stall_id = s.stall_id LEFT JOIN
     rent r
     ON r.tenant_id = t.tenant_id AND r.rent_status = 1
WHERE s.stall_id = 1;

A LEFT JOIN keeps all rows in the first table along with matching rows in the second table. If there is no match, then the values are NULL for the columns in the second table. Your WHERE condition fails on NULL conditions.