Untitled

SELECT 
    j.serial,
    p.commoncolor,
    product,
    j.size,
    j.location,
    j.previousbin,
    j.previouslocation
FROM
    (SELECT 
        CONCAT('s', serialnumber) serial,
            product,
            size,
            location,
            previousbin,
            previouslocation,
            lastscandate
    FROM
        inventoryitem
    WHERE
        location = 'cant find:pending'
            AND previouslocation LIKE '7-%'
            AND qtyonhand = 1
    ORDER BY previouslocation , previousbin ASC) j
        JOIN
    product p ON p.code = j.product