Untitled

mail@pastecode.io avatar
unknown
mysql
a year ago
1.0 kB
7
Indexable
Never
SELECT 
    r.VendorRANum,
    r.scale,
    r.productcode,
    r.qty1,
    r.qty2,
    r.qty3,
    r.qty4,
    r.qty5,
    r.qty6,
    r.qty7,
    r.qty8,
    r.qty10,
    r.qty11,
    r.qty12,
    i.product,
    i.size,
    GROUP_CONCAT(DISTINCT SUBSTRING(SUBSTRING_INDEX(i.locationhistory, ',', 1),
            2,
            50)) AS Locs
FROM
    (SELECT 
        revolveranumber AS InternalRA,
            ravendornumber AS VendorRANUM,
            ratype,
            rascope,
            priority,
            inputdate,
            expiration,
            productcode,
            scale,
            qty1,
            qty2,
            qty3,
            qty4,
            qty5,
            qty6,
            qty7,
            qty8,
            qty10,
            qty11,
            qty12
    FROM
        rarequest
    WHERE
        ravendornumber = 'RFRA555') r
        JOIN
    inventoryitem i ON r.productcode = i.product
WHERE
    location LIKE '7-%' AND qtyonhand = 1
GROUP BY i.product , i.size
ORDER BY i.product ASC