Create a PHP array using left join and MySQL IF statement

Question

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

remove AND o.CustID=p.CustID that makes a natural join from your left join — Jens
– Jens, Commented Jun 15, 2016 at 13:41

spencer7593 · Accepted Answer · 2016-06-15 13:42:17Z

2

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

answered Jun 15, 2016 at 13:42

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rocketman Over a year ago

Thanks, that worked. The USING(CustID) was a typo should have been USING(OrderID)

Rico_93 · Accepted Answer · 2016-06-15 13:51:30Z

0

I'm not sure what you're really having a problem with , but you should consider using a code similar to this to help you with your query , this code should display all that is paid with an id of 123456, if you want you could use the same code and chage 'Y' to "N" to display all that isn't paid.

  "SELECT
        Orders .*,
        Payments .*
        FROM
        tbl_Orders , tblinterviews
        WHERE
        Orders .CustID = 123456 AND Payments .CustID=Orders .CustID AND Orders .Paid='Y'
        Group by Orders .OrderID
    Order by Orders .OrderID;
        ";

answered Jun 15, 2016 at 13:51

Rico_93

1081 silver badge6 bronze badges

Collectives™ on Stack Overflow

Create a PHP array using left join and MySQL IF statement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related