Thanks John, that didn't completely give the right result but that was the kick in the right direction I needed!
One problem I still had was that each line was then creating a zero value entry so there was some duplicate lines; i.e. the table had item1, Location1, 0 and item1, Location1, 10...
Rather than add more to the if statement, grouping the new table worked just fine for me:
LEFT JOIN (Items) LOAD locationID as newLocationID ,locationName RESIDENT Locations ; NewItems: LOAD itemCode ,locationName ,Sum(if(locationID=newLocationID,qty,0)) as qty RESIDENT Items GROUP BY itemCode, locationName ; DROP TABLES Items, Locations;
Thanks for the help