T-SQL Query | [ All Items Puzzle ] – In this puzzle we have find out whether customer purchased any of the item from item table or not, If yes then we have provide the actual quantity otherwise we have to show zero. Please check out the sample input and expected output for details.

Sample Input

Item Table

 ITEM_NO ITEM_DESC 1 Large Bags 2 Medium Bags 3 Large Boxes 4 Medium Boxes

ORDERED Table

 CUST_NO ITEM_NO QTY_ORDERED 1 1 10 1 2 15 1 4 20 2 3 30 2 4 40 3 1 20 3 2 50 3 3 10

Expected Output

 CUST_NO ITEM_NO Oty 1 1 10 2 1 0 3 1 20 1 2 15 2 2 0 3 2 50 1 3 0 2 3 30 3 3 10 1 4 20 2 4 40 3 4 0

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.

Script

Use the below script to generate the source table and fill them up with the sample data.

 ```CREATE TABLE Item ( ITEM_NO CHAR(12) NOT NULL, ITEM_DESC CHAR(30) NULL ) CREATE TABLE ORDERED ( CUST_NO CHAR(15) NOT NULL, ITEM_NO CHAR(12) NOT NULL, QTY_ORDERED INT NULL )   INSERT INTO Item(Item_NO,Item_Desc) VALUES (1, 'Large Bags'), (2, 'Medium Bags'), (3, 'Large Boxes'), (4, 'Medium Boxes')   INSERT INTO ORDERED(CUST_NO,Item_NO,Qty_Ordered) VALUES (1 , 1 , 10), (1 , 2 , 15), (1 , 4 , 20), (2, 3 , 30), (2, 4 , 40), (3, 1 , 20), (3, 2 , 50), (3,3 , 10)   ```

UPDATE – 20-Apr-2015 – Solution 1

 ``` -- --Solution 1 SELECT c.CUST_NO,i.ITEM_NO,ISNULL(SUM(o.QTY_ORDERED),0) Oty FROM ( ( SELECT DISTINCT CUST_NO FROM ORDERED o ) c CROSS JOIN Item i LEFT OUTER JOIN ORDERED o on o.CUST_NO = c.CUST_NO and o.ITEM_NO = i.ITEM_NO ) GROUP BY c.CUST_NO,i.ITEM_NO -- ```

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com