CODEWITHSUNDEEP
sqlsql-serverjoin
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

JOIN not producing results

I have a daily figure view and a monthly figure view.

DAILY view:

 SELECT * (includes sum)
 FROM
 Person
 FULL OUTER JOIN Profit 
 ON Person.id = Profit.id
 AND Profit.[DATE] = CAST(getdate () -1  as Date)

Now this returns 70 rows.

MONTHLY view:

 SELECT * (includes sum)
 FROM
 Person
 FULL OUTER JOIN Profit
 ON Person.id = profit.id
 WHERE 
 [DATE] BETWEEN CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' +  + 
 CAST(YEAR(GETDATE()) AS VARCHAR)
 AND  GETDATE()

This returns 40 rows.

Now in my select statement i want to return all rows from the daily view regardless of whether there is a match in the monthly view.

Now i thought something like this would work:

 SELECT *
 FROM  
 DailyView
 LEFT JOIN
 MonthlyView
 ON DailyView.id = MonthlyView.id

But this only returns 40 rows. I have also tried full outer join but this still only returns 40 rows. What could be the reason for this? Thanks

Upvotes: 0

Views: 67

Answers (2)

Marian Nasry
Marian Nasry

Reputation: 819

The monthly view must 

SELECT * (includes sum)
 FROM
 Person
 FULL OUTER JOIN Profit
 ON Person.id = profit.id
 AND [DATE] BETWEEN CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' +  + 
 CAST(YEAR(GETDATE()) AS VARCHAR)
 AND  GETDATE()

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Your WHERE clause is undoing the outer join.

You can fix your problem by moving the conditions to the ON clause and removing the WHERE clause.

Your query is suspicious in its use of FULL OUTER JOIN. FULL OUTER JOIN is not generally needed when joining tables on well-defined keys.

Upvotes: 2

Related Questions