Ok I get the problem. It is the LIMIT 1 which was misleading me.
If I remove this limit, I get many returned results, some where orders
were paid, some where orders were not paid, therefore the LIMIT1 picks
the first one, and by chance it lands on an unpaid order.
Am I trying to achieve something which is not possible to do? Do I
compulsory need to make 2 queries? One for the order only and one for
the product only?
