Untitled

SELECT 
    p.siteflag,
    CASE
        WHEN p.shipSeparate = 1 THEN 'Y'
        WHEN p.shipSeparate = 0 THEN 'N'
    END AS ShipAlone,
    i.*
FROM
    (SELECT 
        serialnumber,
            product,
            size,
            bin,
            location,
            previousbin,
            previouslocation,
            lastscandate
    FROM
        inventoryitem
    WHERE
        location LIKE '7-LC%' AND qtyonhand = 1) i
        JOIN
    product p ON p.code = i.product
ORDER BY location , ShipAlone ASC