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',
                'preorder',
                'unvalidated')
        THEN
            'Y'
        WHEN
            o.status NOT IN ('ready' , 'picked',
                'printed',
                'printing',
                'On Hold',
                'preorder',
                'unvalidated')
        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
ORDER BY lastscandate ASC