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;