Select one of two subqueries, with joining to another column
I have two queries that pull from three tables:
t1 -sites
t2 - blues
t3 - reds
query1 -
SELECT s.site_name b.year b.value
FROM sites s, blues b
WHERE s.id = b.site_id AND s.site_name ='site1'
returns the 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 |
request 2
SELECT s.site_name r.year r.value
FROM sites s, reds r
WHERE s.id = r.site_id
AND s.site_name ='site1'
return:
| site_name | year | reds |
| site1 | 2012 | 14.0 |
| site1 | 2010 | 11.0 |
| site1 | 2009 | 18.9 |
In the end I want:
| 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 | |
This basically matches all entries in both the blues table and the red table for the site mapped to years, even if one of the tables does not have an entry for that year.
Many thanks to @vkp for pointing me in the right direction: CTE for help.
Here is the tested query
WITH x AS (SELECT s.site_name AS name, b.year AS yr1, b.value AS blue_val
FROM public.sites s JOIN public.blues b
ON s.id = b.site_id
WHERE s.site_name = 'Site1'
), y AS (SELECT s.site_name, r.year AS yr2, r.value AS red_val
FROM public.stations s JOIN public.reds r
ON s.id = r.site_id
WHERE s.site_name = 'Site1'
)
SELECT x.name, x.yr1, x.blue_val, y.red_val
FROM x LEFT JOIN y ON x.yr1 = y.yr2
+3
source to share
3 answers
SELECT s.site_name, b.year, b.value as blues, r.value as reds
FROM sites s right join blues b on s.id = b.site_id
right join reds r on s.id = r.site_id and b.year = r.year
where s.site_name ='site1'
Updated query with CTE:
with x as (SELECT s.site_name, b.year, b.value as blues
FROM sites s join blues b on s.id = b.site_id
where s.site_name ='site1')
, y as (SELECT s.site_name, r.year, r.value as blues
FROM sites s join reds r on s.id = r.site_id
where s.site_name ='site1')
,yrs as (select year from x union select year from y)
select
case when x.site_name is not null then x.site_name
else y.site_name end as site_name
, yrs.year, x.value as blue_val, y.value as red_val
from yrs left join x on x.year = yrs.year
left join y on y.year = yrs.year
+2
source to share
Please forgive me if the syntax isn't quite right - I'm not that familiar with Postgresql.
If you can nest queries:
SELECT
s.site_name,
i.year,
i.blues,
i.reds
FROM
#sites AS s,
(
SELECT
COALESCE( b.site_id, r.site_id ) AS site_id,
COALESCE( b.year, r.year ) AS year,
b.value AS blues,
r.value AS reds
FROM
#blues AS b
FULL OUTER JOIN #reds AS r
ON r.year = b.year AND r.site_id = b.site_id
) AS i
WHERE
s.site_name = 'site1' AND
i.site_id = s.site_id
If you can't, use UNION to separate the types of joins in which you build sets, where both blue and red exist for a year, where only blue exists for a year, and where only red exists for a year:
SELECT
s.site_id,
b.year AS year,
b.value AS blues,
r.value AS reds
FROM
#sites s
INNER JOIN #blues b
ON b.site_id = s.site_id
INNER JOIN #reds r
ON r.site_id = s.site_id AND
r.year = b.year
WHERE
s.site_name = 'site1'
UNION
SELECT
s.site_id,
b.year AS year,
b.value AS blues,
null AS reds
FROM
#sites s
INNER JOIN #blues b
ON b.site_id = s.site_id
WHERE
s.site_name = 'site1' AND
b.year NOT IN ( SELECT year FROM #reds WHERE #reds.site_id = s.site_id )
UNION
SELECT
s.site_id,
r.year AS year,
null AS blues,
r.value AS reds
FROM
#sites s
INNER JOIN #reds r
ON r.site_id = s.site_id
WHERE
s.site_name = 'site1' AND
r.year NOT IN ( SELECT year FROM #blues WHERE #blues.site_id = s.site_id )
0
source to share