Thread: How can I combine these two dependent views into one view?
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 1CREATE 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;
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
www.lithicsireland.ie
ca.linkedin.com/in/killiandriscoll
IRC Postdoctoral Fellow
UCD School of Archaeology
University College Dublin
academia.edu/KillianDriscoll
www.lithicsireland.ie
ca.linkedin.com/in/killiandriscoll