Venkatvasan Venkatvasan - 6 months ago 7
SQL Question

Is there a better way to write this select query

SELECT t1.X,
t1.Item_Code,
t2.X,
t2.Item_Code
FROM (SELECT Sum(e.item_qty) AS X,
A.Item_Code
FROM [Item Master]A
INNER JOIN [Counter Issue Details] e
ON e.Item_Code = A.Item_Code
INNER JOIN [Counter Master] CM
ON CM.Counter_Code = e.Counter_Code
WHERE e.Item_Code = A.Item_Code
AND A.Type_Code = 0
GROUP BY A.Item_Code)t1,
(SELECT Sum(e.item_qty) AS X,
A.Item_Code
FROM [Item Master]A
INNER JOIN [Counter Return Details] e
ON e.Item_Code = A.Item_Code
INNER JOIN [Counter Master] CM
ON CM.Counter_Code = e.Counter_Code
WHERE e.Item_Code = A.Item_Code
AND A.Type_Code = 0
GROUP BY A.Item_Code)t2


Iam getting the outpt has

X Item_Code Y Item_Code
--------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
3998.000 1 2.00 11
2000.000 2 2.00 11
2000.000 3 2.00 11
2504.000 4 2.00 11
2384.000 5 2.00 11
2456.000 6 2.00 11
2372.000 7 2.00 11
4900.000 9 2.00 11
2000.000 10 2.00 11
3948.000 11 2.00 11


Now I am getting duplicate records for Item code 11 as 2 for all the 10 records.
I want the Y value to be mapped on the item code 11 itself and all other should be null

Answer

Your issue is that old style cartesian join which acts as a CROSS JOIN. If you consider a proper join you won't be doubling up your data;

SELECT t1.Item_Code
    ,t1.X t1_x
    ,ISNULL(t2.X,0) t2_x
FROM (
    SELECT SUM(e.item_qty) AS X
        ,A.Item_Code
    FROM [Item Master] A
    INNER JOIN [Counter Issue Details] e ON e.Item_Code = A.Item_Code
    INNER JOIN [Counter Master] CM ON CM.Counter_Code = e.Counter_Code
    WHERE e.Item_Code = A.Item_Code
        AND A.Type_Code = 0
    GROUP BY A.Item_Code
    ) t1

LEFT JOIN   
    (
        SELECT SUM(e.item_qty) AS X
            ,A.Item_Code
        FROM [Item Master] A
        INNER JOIN [Counter Return Details] e ON e.Item_Code = A.Item_Code
        INNER JOIN [Counter Master] CM ON CM.Counter_Code = e.Counter_Code
        WHERE e.Item_Code = A.Item_Code
            AND A.Type_Code = 0
        GROUP BY A.Item_Code
        ) t2
ON t1.Item_Code = t2.Item_Code

This will return all values from t1, and matching information from t2. Edit: t2.X will now show 0 with no data rather than NULL as requested.

Comments