How can I combine these two dependent views into one view?

From: Killian Driscoll <killian(dot)driscoll(at)ucd(dot)ie>
To: pgsql-novice(at)postgresql(dot)org
Subject: How can I combine these two dependent views into one view?
Date: 2016-01-04 22:25:01
Message-ID: CAFTc7Ady3d2hQXjA8jOmY5NX3ONX_PHLfUeLq8-2WU8HAVUzsw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I have two views (used to extract rows from a number of tables including a
N:M junction table), the second of which is dependent on the first, but I
would like to know how I can write the sql to use just one view instead of
two.

View 1

CREATE OR REPLACE VIEW irll.query_srpnt_sample_lir AS
SELECT surveypoint.surveypoint_id AS surv_id,
sample.sample_id AS samp_id
FROM surveypoint
LEFT JOIN sample ON surveypoint.surveypoint_id = sample.surveypoint_id;

View 2 - which you can see View # 1 is named as query 1 on the LEFT JOIN

CREATE OR REPLACE VIEW irll.query_surv_sam_image_lir_sam AS
SELECT DISTINCT ON (junc1.svy_sam_image_junc_id) fi1.image_name,
fi1.iptc_caption,
fi1.iptc_keywords,
junc1.svy_sam_image_junc_id,
junc1.surveypoint_id,
query1.surv_id,
junc1.sample_id,
query1.samp_id
FROM svy_sam_image_junc junc1
JOIN fldwrk_image fi1 ON fi1.fldwrk_image_id = junc1.fldwrk_image_id
LEFT JOIN query_srpnt_sample_lir query1 ON query1.surv_id =
junc1.surveypoint_id
WHERE junc1.sample_id IS NULL AND fi1.iptc_keywords::text ~~
'%geological%sample%'::text
UNION
SELECT DISTINCT ON (junc1.svy_sam_image_junc_id) fi1.image_name,
fi1.iptc_caption,
fi1.iptc_keywords,
junc1.svy_sam_image_junc_id,
junc1.surveypoint_id,
query1.surv_id,
junc1.sample_id,
query1.samp_id
FROM svy_sam_image_junc junc1
JOIN fldwrk_image fi1 ON fi1.fldwrk_image_id = junc1.fldwrk_image_id
LEFT JOIN query_srpnt_sample_lir query1 ON query1.samp_id =
junc1.sample_id
WHERE junc1.sample_id IS NOT NULL AND fi1.iptc_keywords::text ~~
'%geological%sample%'::text
ORDER BY 4;

--
Killian DriscoIl
IRC Postdoctoral Fellow
UCD School of Archaeology
University College Dublin

academia.edu/KillianDriscoll <https://ucd.academia.edu/KillianDriscoll>
www.lithicsireland.ie
ca.linkedin.com/in/killiandriscoll

Browse pgsql-novice by date

  From Date Subject
Next Message Jürgen Purtz 2016-01-05 19:05:37 Re: Graphic to visualize data flow between processes, buffers and files
Previous Message Tom Lane 2016-01-04 15:46:00 Re: Graphic to visualize data flow between processes, buffers and files