Untitled

SELECT 
    i.serial,
    p.commoncolor AS color,
    i.product,
    i.size,
    i.bin,
    i.location,
    i.lastscandate
FROM
    (SELECT 
        CONCAT('s', serialnumber) serial,
            product,
            size,
            bin,
            location,
            lastscandate
    FROM
        inventoryitem
    WHERE
        location LIKE '10-%' AND qtyonhand = 1
    GROUP BY location , bin
    HAVING COUNT(serialnumber) = 1) i
        JOIN
    product p ON p.code = i.product