Multiple Joins In Subquery

I am writing a query that brings in period from another table, so if this year then period is 201901 - 201912 next year = 202001 - 202012 and last year is 201801 - 201812 this is the financial period.

Currently the GL period table shows 201903 i.e. June 2018. I want to write a query so I can get this year next and last year dates my join can only return ONE i.e. either this year or next year or last year, however I want it to return all three. How do I do this?

SELECT 
yearfrom,
period,
fld_from,
fld_to,
fld_desc,
[ThisYear] = ((select(LEFT(Gl_GLPARAM.PERIOD,4)) from Gl_GLPARAM)),
[LastYear] = ((select(LEFT(Gl_GLPARAM.PERIOD,4))-1 from Gl_GLPARAM)),
[NextYear] = ((select(LEFT(Gl_GLPARAM.PERIOD,4))+1 from Gl_GLPARAM))


FROM GL_YEARPDET
right join (select(LEFT(Gl_GLPARAM.PERIOD,4)) as 'ThisYear' from Gl_GLPARAM) 
as T1 on GL_YEARPDET.YearFrom = t1.ThisYear

I need this same as above somehow added to the subquery

right join (select(LEFT(Gl_GLPARAM.PERIOD,4))+1 as 'NextYear' from 
Gl_GLPARAM) as T2 on GL_YEARPDET.YearFrom = t2.NextYear
right join (select(LEFT(Gl_GLPARAM.PERIOD,4))+1 as 'LastYear' from 
Gl_GLPARAM) as T3 on GL_YEARPDET.YearFrom = t3.LastYear

SqlSubqueryresult

SampleTable

1 answer

  • answered 2018-07-11 03:51 Reg Chand

     FROM GL_YEARPDET
     where YearFrom IN( ((select(LEFT(Gl_GLPARAM.PERIOD,4)) from Gl_GLPARAM)), 
     ((select(LEFT(Gl_GLPARAM.PERIOD,4))-1 from Gl_GLPARAM)), 
     ((select(LEFT(Gl_GLPARAM.PERIOD,4))+1 from Gl_GLPARAM)))
    

    where clause has fixed my issue.