SHARE
TWEET

Untitled

a guest Jul 23rd, 2019 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH cte_inf_objects AS (
  2.     SELECT
  3.         o.id                AS inf_object_id,
  4.         o.mo_affiliation_id AS geo_tag_id,
  5.         cwss.name           AS cwss_name,
  6.         o.name              AS object_name,
  7.         o.type_id           AS object_type_id,
  8.         o.object_id         AS object_id
  9.     FROM pure_water.inf_object o
  10.         JOIN pure_water.cwss cwss ON cwss.id = o.cwss_affiliation_id
  11.     WHERE
  12.         o.deleted_at IS NULL
  13.       AND cwss.deleted_at IS NULL
  14. ), cte_match AS (
  15.     SELECT
  16.         cio.inf_object_id AS inf_object_id,
  17.         pwpdo.id          AS documents_object_id
  18.     FROM pure_water.pure_water_program_documents_object pwpdo
  19.         LEFT JOIN cte_inf_objects cio ON
  20.             cio.geo_tag_id = pwpdo.geo_tag_id
  21.             AND cio.cwss_name = pwpdo.cwss_name
  22.             AND cio.object_name = pwpdo.object_name
  23.             AND cio.object_type_id = pwpdo.object_type_id
  24.             AND cio.object_id = pwpdo.object_id
  25.     WHERE pwpdo.deleted_at IS NULL
  26. ), cte_not_unique AS (
  27.     SELECT
  28.         documents_object_id
  29.     FROM cte_match
  30.     GROUP BY documents_object_id
  31.     HAVING count(documents_object_id) >1
  32. )
  33. SELECT
  34.     *
  35. FROM cte_match m
  36. WHERE m.documents_object_id IN (SELECT documents_object_id FROM cte_not_unique)
  37. ORDER BY documents_object_id;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top