Is there a better way to write this select query

Question

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

What are you trying to accomplish? I might suggest that you ask *another* question (this one already has answers) and explain what you are doing, providing sample data and desired results. — Gordon Linoff, Apr 27 '16 at 11:25

score 5 · Accepted Answer · edited Nov 10 '18 at 22:06

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.

There are some great resources around on this subject;

Thank You so much Mr.Benner it works perfectly.You saved my day — Venkatvasan, Apr 27 '16 at 12:16
change the select to 'ISNULL(t2.X, 0) t2_x'. I've edited my original code to show you the example. — Rich Benner, May 13 '16 at 07:30

score 3 · Answer 2 · answered Apr 27 '16 at 11:22

You've got no JOIN specified between your derived tables t1 and t2. You need to use a LEFT JOIN if you expect results from t1, but not t2 for every row.

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
        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;

Is there a better way to write this select query

2 Answers2