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.
Comments
Post a Comment