Untitled

SELECT 
    i.user,
    i.serial,
    i.product,
    i.size,
    i.location,
    i.previouslocation,
    MIN(i.lastscandate) AS lastscandate,
    s.sellableqty,
    s.quantity AS onhand,
    IF(SUM(h.status = ('ready')) > 0,
        'Y',
        'N') AS pending_hold,
    IF(SUM(o.status IN ('ready' , 'cant find',
            'verify',
            'printed',
            'printing',
            'picked',
            'picksorted',
            'on hold',
            'preorder',
            'unvalidated')) > 0,
        'Y',
        'N') AS pending_order,
    GROUP_CONCAT(DISTINCT o.status) AS statuses
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
        JOIN
    helditem h ON h.productcode = i.product
        AND h.option1 = i.size
GROUP BY product , size
ORDER BY lastscandate ASC