Sql-server – Query Only Returning Matching Data

sql serversql-server-2008-r2t-sql

I am attempting to use a Left Outer Join to return data from two tables. MetaKnight has the most storeNames and I want all of those to be returned, and of course the corresponding data from KnightNinja. I threw together the below syntax, but my syntax is only returning the name of the stores in both tables that meet the criteria. For example, Store D & Store E should be returned since the date is in January of 2017 – but the syntax does not return it. Why?

Here is sample garbage DDL & Query

Create Table MetaKnight
(
    storeName varchar(500) NOT NULL
    ,storeNumber varchar(100) NOT NULL Primary Key
    ,openDate date NOT NULL
)

Create NonClustered Index snIndex
On MetaKnight (storeName ASC)

Create Table KnightNinja 
(
    storeName varchar(500) NOT NULL
    ,ssID varchar(100) NOT NULL Primary Key
    ,c1 int not null Default(0)
    ,c2 int not null Default(0)
    ,c3 int not null Default(0)
    ,c4 int not null Default(0)
    ,d1 date not null
)

Create NonClustered Index storeIndex
On KnightNinja (storeName ASC)

Insert Into KnightNinja (storeName, ssID, c1, c2, c3, c4, d1) Values
('Store A', '1234', '7018', '0',    '4635', '6',    '2017-03-13'),
('Store B', '5678', '0',    '0',    '0',    '0',    '2017-01-28'),
('Store C', '9101', '219',  '898',  '154',  '11',   '2017-01-10'),
('Store D', '8842', '25019',    '258947',   '5746', '0',    '2017-01-31'),
('Store E', '6643', '26682',    '25920',    '3410', '281',  '2017-02-15'),
('Store F', '2211', '57',   '0',    '392',  '22',   '2017-03-11'),
('Store G', '1432', '398',  '1007', '407',  '0',    '2017-01-10'),
('Store G', '8879', '534',  '3125', '954',  '19',   '2017-03-11'),
('Store H', '8675', '40',   '0',    '146',  '147',  '2017-01-01'),
('Store I', '2411', '0',    '4899', '0',    '0',    '2017-01-20')

INSERT INTO MetaKnight (storeName, storeNumber, openDate) Values
('Store A', '2.2',  '2017-01-19'),
('Store B', '8103', '2017-10-25'),
('Store C', '5522', '2017-10-25'),
('Store D', 'Blue', '2017-09-26'),
('Store E', 'B6565','2017-04-28'),
('Store G', 'E999', '2017-01-14'),
('Store G', 'L1354','2017-01-14'),
('Store G', 'R2288','2017-01-14'),
('Store H', 'A5678','2017-01-28'),
('Store I', 'C1234','2017-01-14')

Select 
mk.storeName
,[Ninja Count] = COUNT(kn.ssID)
,[Meta Count] = COUNT(mk.storeNumber)
,[C1 Total] = SUM(COALESCE(kn.c1,0))
,[C2 Total] = SUM(COALESCE(kn.c2,0))
,[C3 Total] = SUM(COALESCE(kn.c3,0))
,[C4 Total] = SUM(COALESCE(kn.c4,0))
FROM MetaKnight mk
LEFT OUTER JOIN KnightNinja kn
ON mk.storeName = kn.storeName
AND kn.d1 >= '2017-01-01'
AND kn.d1 < '2017-02-01'
WHERE mk.openDate >= '2017-01-01'
AND mk.openDate < '2017-02-01'
GROUP BY mk.storeName
ORDER BY mk.storeName ASC

Drop Table KnightNinja
Drop Table MetaKnight

Best Answer

Have a look at this interesting answer of Aaron Bertrand and you'll get a detailed answer about what is happening.

Return All Data From Left Table

You should move all conditions of WHERE clause to the LEFT JOIN clause.

Select mk.storeName
       ,[Ninja Count] = COUNT(kn.ssID)
       ,[Meta Count] = COUNT(mk.storeNumber)
       ,[C1 Total] = SUM(COALESCE(kn.c1,0))
       ,[C2 Total] = SUM(COALESCE(kn.c2,0))
       ,[C3 Total] = SUM(COALESCE(kn.c3,0))
       ,[C4 Total] = SUM(COALESCE(kn.c4,0))
FROM   MetaKnight mk
LEFT OUTER JOIN KnightNinja kn
ON     mk.storeName = kn.storeName
AND    kn.d1 >= '2017-01-01'
AND    kn.d1 < '2017-02-01'
AND    mk.openDate >= '2017-01-01'
AND    mk.openDate < '2017-02-01'
GROUP BY mk.storeName
ORDER BY mk.storeName ASC

GO

storeName | Ninja Count | Meta Count | C1 Total | C2 Total | C3 Total | C4 Total
:-------- | ----------: | ---------: | -------: | -------: | -------: | -------:
Store A   |           0 |          1 |        0 |        0 |        0 |        0
Store B   |           0 |          1 |        0 |        0 |        0 |        0
Store C   |           0 |          1 |        0 |        0 |        0 |        0
Store D   |           0 |          1 |        0 |        0 |        0 |        0
Store E   |           0 |          1 |        0 |        0 |        0 |        0
Store G   |           3 |          3 |     1194 |     3021 |     1221 |        0
Store H   |           1 |          1 |       40 |        0 |      146 |      147
Store I   |           1 |          1 |        0 |     4899 |        0 |        0

Warning: Null value is eliminated by an aggregate or other SET operation.

dbfiddle here

Update

On this case there is a problem because none of your queries returns all storeName.

SELECT    mk.storeName,
         [Meta Count] = COUNT(mk.storeNumber)
FROM      MetaKnight mk
WHERE     mk.openDate >= '2017-01-01'
AND       mk.openDate < '2017-02-01'
GROUP BY  mk.storeName;

storeName | Meta Count
:-------- | ---------:
Store A   |          1
Store G   |          3
Store H   |          1
Store I   |          1

SELECT   kn.storeName, 
         [Ninja Count] = COUNT(kn.ssID),
         [C1 Total] = SUM(COALESCE(kn.c1,0)),
         [C2 Total] = SUM(COALESCE(kn.c2,0)),
         [C3 Total] = SUM(COALESCE(kn.c3,0)),
         [C4 Total] = SUM(COALESCE(kn.c4,0))
FROM     KnightNinja kn
WHERE    kn.d1 >= '2017-01-01'
AND      kn.d1 < '2017-02-01'
GROUP BY kn.storeName
GO

storeName | Ninja Count | C1 Total | C2 Total | C3 Total | C4 Total
:-------- | ----------: | -------: | -------: | -------: | -------:
Store B   |           1 |        0 |        0 |        0 |        0
Store C   |           1 |      219 |      898 |      154 |       11
Store D   |           1 |    25019 |   258947 |     5746 |        0
Store G   |           1 |      398 |     1007 |      407 |        0
Store H   |           1 |       40 |        0 |      146 |      147
Store I   |           1 |        0 |     4899 |        0 |        0

Let me suggest another approach:

WITH sn AS
(
    SELECT DISTINCT storeName
    FROM MetaKnight
)
SELECT sn.storeName,               
       kn1.[Ninja Count],
       cnt.[Meta Count],
       kn1.[C1 Total],
       kn1.[C2 Total],
       kn1.[C3 Total],
       kn1.[C4 Total]
FROM   sn
LEFT JOIN (SELECT    mk.storeName
                    ,[Meta Count] = COUNT(mk.storeNumber)
           FROM      MetaKnight mk
           WHERE     mk.openDate >= '2017-01-01'
           AND       mk.openDate < '2017-02-01'
           GROUP BY  mk.storeName) cnt
ON        sn.storeName = cnt.storeName
LEFT JOIN (SELECT   kn.storeName, 
                    [Ninja Count] = COUNT(kn.ssID),
                    [C1 Total] = SUM(COALESCE(kn.c1,0)),
                    [C2 Total] = SUM(COALESCE(kn.c2,0)),
                    [C3 Total] = SUM(COALESCE(kn.c3,0)),
                    [C4 Total] = SUM(COALESCE(kn.c4,0))
           FROM     KnightNinja kn
           WHERE    kn.d1 >= '2017-01-01'
           AND      kn.d1 < '2017-02-01'
           GROUP BY kn.storeName) kn1
ON        sn.storeName = kn1.storeName
ORDER BY  sn.storeName ASC

GO

storeName | Ninja Count | Meta Count | C1 Total | C2 Total | C3 Total | C4 Total
:-------- | ----------: | ---------: | -------: | -------: | -------: | -------:
Store A   |        null |          1 |     null |     null |     null |     null
Store B   |           1 |       null |        0 |        0 |        0 |        0
Store C   |           1 |       null |      219 |      898 |      154 |       11
Store D   |           1 |       null |    25019 |   258947 |     5746 |        0
Store E   |        null |       null |     null |     null |     null |     null
Store G   |           1 |          3 |      398 |     1007 |      407 |        0
Store H   |           1 |          1 |       40 |        0 |      146 |      147
Store I   |           1 |          1 |        0 |     4899 |        0 |        0

dbfiddle here

Related Question