CODEWITHSUNDEEP
mysqlleft-join
mckeegan375
mckeegan375

Reputation: 255

MySQL Left Join not pulling in result

I have a MySQL query where i'm trying to lookup a products name in one table from another table based on it's ID as follows:

mysqli_query("SELECT product, expirydate, SUM(quantity), status FROM
   stockmovement a LEFT JOIN (SELECT  productid, product AS productname FROM
   products) b ON a.product = b.productid WHERE a.status = '0' GROUP BY 
   a.product, a.expirydate HAVING SUM(a.quantity) > 0 ORDER BY a.product,
   a.expirydate ASC");

Everything works apart from the left join which returns blank when i try to output 'productname'. Can anybody see where the query is going wrong?

Thanks in advance and Merry Christmas :)

Upvotes: 0

Views: 31

Answers (1)

Rahul
Rahul

Reputation: 77936

Shouldn't your query be something like below rather

SELECT a.product, a.expirydate, 
SUM(a.quantity), a.status, b.product as productname 
FROM stockmovement a 
LEFT JOIN products b ON a.product = b.productid 
WHERE a.status = '0' 
GROUP BY a.product, a.expirydate 
HAVING SUM(a.quantity) > 0 
ORDER BY a.product, a.expirydate;

Upvotes: 2

Related Questions