Fix My Sql Query 03.07.08
i have attatched the query. the problem i have is it appears to be repeating every MEASURe for every item. it should only be showing the measures the items have records for. but as you can see here each item is repeated about 12.
(SELECT
ITEMS.CUSTNUM1, ITEMS.ITEMNO, ITEMS.DESCRIPT,
ITEMS.IMAGEORIG, ITEMS.CATEGORY,ITEMS.CUSTNUM6,ITEMS.CUSTNUM7,ITEMS.SALE_MEAS,ITEMS.CUSTCHAR16 AS LEATHERWEIGHT,MEASURE_FACTOR.CONV_FACTOR As Meas_Factor
FROM ITEMS,MEASURE_FACTOR
WHERE ITEMS.ACTIVE = ‘T’ AND ITEMS.WEB_ACTIVE = ‘T’ AND ITEMS.CATEGORY = ‘$c’ AND ITEMS.CUSTCHAR3 LIKE ‘$SITECODE’
AND (ITEMS.MATRIX_ITEM_TYPE = ‘0′ OR ITEMS.MATRIX_ITEM_TYPE = ‘1′ OR ITEMS.MATRIX_ITEM_TYPE = ‘3′) AND MEASURE_FACTOR.MEAS_CODE = ITEMS.SALE_MEAS)
union
(SELECT ITEM_EQ.CUSTNUM1, ITEM_EQ.ITEMNO, ITEM_EQ.DESCRIPT, ITEMS.DESCRIPT, ITEM_EQ.CATEGORY,
ITEM_EQ.CUSTNUM6,ITEM_EQ.CUSTNUM7,ITEM_EQ.MEAS_CODE AS SALE_MEAS,ITEM_EQ.CUSTCHAR4 AS LEATHERWEIGHT, MEASURE_FACTOR.CONV_FACTOR As Meas_Factor
FROM
ITEM_EQ, ITEMS, MEASURE_FACTOR
WHERE ITEMS.ITEMNO = ITEM_EQ.ITEMNO AND ITEM_EQ.ACTIVE = ‘T’ AND ITEM_EQ.WEB_ACTIVE = ‘T’ AND ITEM_EQ.CATEGORY = ‘$c’
AND ITEM_EQ.CUSTCHAR3 LIKE ‘$SITECODE’ AND MEASURE_FACTOR.MEAS_CODE = ITEM_EQ.MEAS_CODE)
ORDER BY CUSTNUM1, DESCRIPT ASC
