Untitled

SELECT 
    i.user,
    i.serial,
    i.product,
    i.size,
    i.location,
    i.previouslocation,
    i.lastscandate,
    s.sellableqty,
    s.quantity AS onhand,
    CASE
        WHEN o.status IN ('ready' , 'picked', 'printed', 'printing', 'On Hold') THEN 'Y'
        WHEN o.status NOT IN ('ready' , 'picked', 'printed', 'printing', 'On Hold') THEN 'N'
    END AS 'order'
FROM
    (SELECT 
        CONCAT('s', serialnumber) serial,
            product,
            size,
            bin,
            location,
            previouslocation,
            lastscandate,
            SUBSTRING_INDEX(SUBSTRING_INDEX(locationhistory, ']', 1), ',', - 1) user
    FROM
        inventoryitem
    WHERE
        location = 'preferred sellable'
            AND qtyonhand = 1
    ORDER BY lastscandate ASC) i
        JOIN
    inventory s ON s.product = i.product
        AND i.size = s.option1
        JOIN
    shipment o ON o.productcode = i.product
        AND o.option1 = i.size
#GROUP BY i.serial
ORDER BY lastscandate ASC