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
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 |