Rocketman Rocketman - 5 months ago 29
SQL Question

Create a PHP array using left join and MySQL IF statement

Here is my question

Let's say I have these two MySQL tables

Orders
CustID, OrderID, OrderDetails1, OrderDetails2, ..
1, 101, Item1, Item2, ...
1, 102, Item1, Item2, ...
1, 103, Item1, Item2, ...
2, 104, Item1, Item2, ...
2, 105, Item1, Item2, ...
.... and so on

Payments
OrderID, Paid
101, Y
105, Y
.... and so on


Now I want an array that contains all the orders (per customer) with column Paid Y or N

Array => {
(CustID=1, OrderID=101, Paid=Y),
(CustID=1, OrderID=102, Paid=N),
(CustID=1, OrderID=103, Paid=N) }


My current SQL is

SELECT o.CustID, o.OrderID, if(p.Paid='Y','Y','N') as Paid
FROM Orders o left JOIN Payments p USING(CustID)
WHERE o.CustID = 123456
AND o.CustID=p.CustID
Group by o.OrderID
Order by o.OrderID;


The issue is that the above SQL returns Y for all rows in the Array. Surely the IF statement within the MySQL should check and replace with 'N' if no record is present in the Payments table for a given OrderID.

OR

Do I have to do this in PHP in two steps. Need to avoid this as this will be a high-traffic function.

Thanks

Answer

I'm not understanding USING(CustID) in the join condition. The Payments table shown doesn't show a CustID column.

In the WHERE clause, this condition o.CustID=p.CustID will only be satisfied for non-NULL value of CustID. And that negates the "outerness" of the join, rendering it equivalent to an inner join.

And the inclusion of a GROUP BY clause is odd. Other databases would throw an error (something along the lines of "non-aggregate in SELECT list")

Just fix your query.

   SELECT o.CustID
        , o.OrderID
        , IF(p.Paid='Y','Y','N') AS Paid 
     FROM Orders o
     LEFT 
     JOIN Payments p
       ON p.OrderID = o.OrderID
      AND p.CustID  = o.CustID     -- ? is OrderID not unique
    WHERE o.CustID = 123456
    ORDER BY o.OrderID
Comments