i have 2 queries pulling 3 tables:
t1 -sites t2 - blues t3 - reds query1 -
select s.site_name b.year b.value sites s, blues b s.id = b.site_id , s.site_name ='site1' returning following (eg.)
| site_name | year | blues | | site1 | 2012 | 23.6 | | site1 | 2011 | 19.1 | | site1 | 2010 | 10.2 | | site1 | 2009 | 25.8 | | site1 | 2008 | 14.0 | query 2
select s.site_name r.year r.value sites s, reds r s.id = r.site_id , s.site_name ='site1' returning:
| site_name | year | reds | | site1 | 2012 | 14.0 | | site1 | 2010 | 11.0 | | site1 | 2009 | 18.9 | i want this:
| site_name | year | blues | reds | | site1 | 2012 | 23.6 | 14.0 | | site1 | 2011 | 19.1 | | | site1 | 2010 | 10.2 | 11.0 | | site1 | 2009 | 25.8 | 18.9 | | site1 | 2008 | 14.0 | | which amounts records in both blues , reds table site, matched on years when 1 of tables not have record year.
many @vkp pointing me in right direction: ctes rescue.
here tested query
with x (select s.site_name name, b.year yr1, b.value blue_val public.sites s join public.blues b on s.id = b.site_id s.site_name = 'site1' ), y (select s.site_name, r.year yr2, r.value red_val public.stations s join public.reds r on s.id = r.site_id s.site_name = 'site1' ) select x.name, x.yr1, x.blue_val, y.red_val x left join y on x.yr1 = y.yr2
select s.site_name, b.year, b.value blues, r.value reds sites s right join blues b on s.id = b.site_id right join reds r on s.id = r.site_id , b.year = r.year s.site_name ='site1' updated query cte:
with x (select s.site_name, b.year, b.value blues sites s join blues b on s.id = b.site_id s.site_name ='site1') , y (select s.site_name, r.year, r.value blues sites s join reds r on s.id = r.site_id s.site_name ='site1') ,yrs (select year x union select year y) select case when x.site_name not null x.site_name else y.site_name end site_name , yrs.year, x.value blue_val, y.value red_val yrs left join x on x.year = yrs.year left join y on y.year = yrs.year 
Comments
Post a Comment