CODEWITHSUNDEEP
sql-serversql-server-2005
Prateek
Prateek

Reputation: 154

SQL left join filters the unmatching records (missing null)

I have two tables.

  1. Calendar : This table contains the date for a year.
  2. VisitorTable : This table has date and Count of persons visited.

In the visitor table for few days there are no records.

The date range which has issue is 22-Aug-2014 till 30-Sep-2014.

The date are stored in below format

'2014-08-21 00:00:00.000' and '2014-10-17 00:00:00.000'

On left join of Calendar table with VisitorTable I am not getting null values for the given date range. The result is present as below

SQL Query

SELECT VisitingDate ActivityDate
,COUNT(Visit) AS Visits  
FROM CALENDAR A
  LEFT JOIN VisitorTable b
ON B.VisitingDate= A.[DATE]
  WHERE b.StoreName = 'ABC'

Output

ActivityDate Visits
2014-10-16 00:00:00.000 63
2014-09-30 00:00:00.000 69
2014-08-22 00:00:00.000 51

Calendar Table

SELECT *
FROM CALENDAR A
WHERE A.[DATE] BETWEEN '2014-08-22 00:00:00.000'
        AND '2014-09-30 00:00:00.000'

Output

DATE
2014-08-22 00:00:00.000
2014-08-23 00:00:00.000
2014-08-24 00:00:00.000
2014-08-25 00:00:00.000
2014-08-26 00:00:00.000
2014-08-27 00:00:00.000 .
.
.
2014-09-28 00:00:00.000
2014-09-29 00:00:00.000
2014-09-30 00:00:00.000

I am plotting a grpah, thus I need dates with zero vistis.

Pointers will help.

Thanks in advance.

Upvotes: 0

Views: 2299

Answers (3)

Prateek
Prateek

Reputation: 154

Final Query

WITH Gross_Visitor
AS (
    SELECT *
    FROM dbo.VisitorTable a
    WHERE a.StoreName = 'ABC'
        AND A.ActivityDate BETWEEN '2014-08-22 00:00:00.000'
            AND '2014-10-20 00:00:00.000'
    )
SELECT *
FROM (
    SELECT C.DATE ActivityDate
        ,COUNT(Visit) AS Visits  
    FROM CALENDAR C
    LEFT JOIN Gross_Visitor O
        ON O.ActivityDate = C.[DATE]
    ) X
    WHERE X.ActivityDate BETWEEN '2014-08-22 00:00:00.000'
            AND '2014-10-20 00:00:00.000'
ORDER BY X.ActivityDate desc;

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32155

Your WHERE clause is forcing an implicit INNER JOIN. You need to handle NULL values in the WHERE clause or specify your conditions as a part of the LEFT JOIN.

Try: 

SELECT VisitingDate ActivityDate
    ,COUNT(Visit) AS Visits  
FROM CALENDAR A
LEFT JOIN VisitorTable b
    ON B.VisitingDate= A.[DATE]
    AND b.StoreName = 'ABC'
GROUP BY VisitingDate

Or:

SELECT VisitingDate ActivityDate
    ,COUNT(Visit) AS Visits  
FROM CALENDAR A
LEFT JOIN VisitorTable b
    ON B.VisitingDate= A.[DATE]
WHERE b.StoreName = 'ABC'
    OR b.StoreName IS NULL
GROUP BY VisitingDate

Upvotes: 1

Kay
Kay

Reputation: 413

With left join you'll, everything in left table plus anything that matches in the right table.

Your requirement looks like, you need full join or try swapping the joins.

Upvotes: 0

Related Questions