MySQL Left Join not pulling in result

Question

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 :)

You are joining a.product to b.productid... is that correct? Without knowing the data, it looks like you'd want b.product instead. — user5886152
– user5886152, Commented Dec 24, 2016 at 19:17
Sorry for the confusing field names. 'product' in the stockmovement table is an ID which i'm trying to join to 'productid' in the products table and pull 'product' which is the products name within the products table. — mckeegan375
– mckeegan375, Commented Dec 24, 2016 at 19:20

Rahul · Accepted Answer · 2016-12-24 19:24:42Z

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;

Collectives™ on Stack Overflow

MySQL Left Join not pulling in result

1 Answer 1

Comments

Hot Network Questions