CODEWITHSUNDEEP
sqlt-sqljoinleft-join
Neo
Neo

Reputation: 16239

Will condition in JOIN carried forward for next JOIN in sql?

Hi I have one sql query and i'm stuck with one condition as i'm new to sql

select * from Today
LEFT JOIN
        TABLEPricing Yesterday  ON 
        Yesterday.Date = (select top 1 date from #Dates where MONTH(DATE) = MONTH(Today.Date)-1 AND YEAR(DATE) = YEAR(Today.Date))
LEFT JOIN Prev ON
        Prev.Date = Yesterday.Date

In second condition Prev.Date = Yesterday.Date will Yesterday.Date take automatically previously assign expression for Yesterday.Date like Yesterday.Date = (select top 1 date from #Dates where MONTH(DATE) = MONTH(Today.Date)-1 AND YEAR(DATE) = YEAR(Today.Date))

or I need to again specify the same condition like below.

Prev.Date = (select top 1 date from #Dates where MONTH(DATE) = MONTH(Today.Date)-1 AND YEAR(DATE) = YEAR(Today.Date))

please clarify i'm confused.

Upvotes: 0

Views: 102

Answers (2)

Devolus
Devolus

Reputation: 22104

I have to revoke my previous answer, as I thought you had to write explicitly OUTER to make it an outer join. So ElectricLlama is right. You have to state the clause again. Or in this case you can simply remove the LEFT to make it a normal join.

Upvotes: 2

Nick.Mc
Nick.Mc

Reputation: 19194

That's incorrect. Since it's an outer join, the value in Yesterday.Date will be NULL if there is no match on TABLEPricing. So your next join to Prev will fail to ever match anything.

Since you are using an outer join you indeed need to use the full expression.

I'm sure you could rewrite this to be more efficient. Are you trying to identify the prior month? Is it based on a calendar month?

Upvotes: 1

Related Questions