Question :
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
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 GOstoreName | 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 GOstoreName | 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 GOstoreName | 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