join - How to return all of one column, some of another in sql -

i have following tables:

geometry

| id | geoid | year | value | |  1 |   100 | 2010 |    76 | |  2 |   100 | 2020 |     1 | |  3 |   101 | 2010 |    73 | |  4 |   101 | 2020 |   123 | |  5 |   102 | 2010 |     4 | |  6 |   102 | 2020 |    20 | 

years

| year | | 2010 | | 2020 | 

regions

| id | geoid | region | |  1 |   100 |      1 | |  2 |   101 |      1 | |  3 |   102 |      1 | |  4 |   100 |      2 | |  5 |   101 |      2 | |  6 |   102 |      3 | 

i want geoid, year, , value region = 2. here's sql:

select distinct reg.geoid, years.year, j.value regions reg, years left join (   select geometry.geoid, geometry.year, geometry.value   geometry   join regions reg   on reg.geoid = geometry.geoid   , reg.region = 2   ) j on j.year = years.year reg.region = 1 , reg.geoid = j.geoid 

and gives me this:

| geoid | year | value | |   100 | 2010 |    76 | |   100 | 2020 |     1 | |   101 | 2010 |    73 | |   101 | 2020 |   123 | 

but want geoids , years. that's why did left join, returns of rows of left table. expected return this:

| geoid | year | value | |   100 | 2010 |    76 | |   100 | 2020 |     1 | |   101 | 2010 |    73 | |   101 | 2020 |   123 | |   102 | 2010 |  null | |   102 | 2020 |  null | 

how can achieve this?

update

here's sqlfiddle of above data: http://sqlfiddle.com/#!2/963c7/6

the issue you've use clause includes table trying left join to. makes work inner join statement instead.

specifically and reg.geoid = j.geoid.

move join statement instead , might have better luck. of course cross join isn't explicit , causes errors in sql gave, following work though:

select distinct regyears.geoid, regyears.year, j.value ( select reg.geoid, reg.region, years.year @regions reg, @years years ) regyears left join (   select geometry.geoid, geometry.year, geometry.value   @geometry geometry   join @regions reg   on reg.geoid = geometry.geoid   , reg.region = 2   ) j on j.year = regyears.year , j.geoid = regyears.geoid regyears.region = 1 

i've used temporary objects test this, @regions, @years, @geometry. replace table names.