0

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
5

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

  • Excellent - thank you for that. In your opinion since I am constantly filtering on either opendate or d1 would it be beneficial to add index both of those columns as well? – BellHopByDayAmetuerCoderByNigh May 11 '17 at 14:19
  • I am getting different results when I use this same query on my production data. Any thoughts on what could be the culprit? – BellHopByDayAmetuerCoderByNigh May 11 '17 at 14:50
  • no - the stores only exist in these two tables. – BellHopByDayAmetuerCoderByNigh May 11 '17 at 15:14
  • Let me pose another ? since your answer solved my ? with the sample DDL - I will add in different DDL this time to illustrate my issue. – BellHopByDayAmetuerCoderByNigh May 11 '17 at 15:19
  • 1
    Have a look at last query: dbfiddle.uk/… – McNets May 11 '17 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.