 ORDER BY s_concept_name
 ;
 
+
+-- CRM preparation
+with taba AS (SELECT c.concept_code AS concept_code_1,
+                     cc.concept_code AS concept_code_2,
+                     c.vocabulary_id AS vocabulary_id_1,
+                     cc.vocabulary_id  AS vocabulary_id_2,
+                     CASE
+                         WHEN cc.standard_concept = 'S'   then 'Maps to'
+                         ELSE concat('CAP', ' to ', coalesce(cc.vocabulary_id, 'NULL'), ' equivalent')
+                         END                         AS relationship_id,
+                     TO_DATE('20200427', 'yyyymmdd') AS valid_start_date,
+                     TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
+                     NULL                            as invalid_reason
+              FROM cap_breast_mapping m
+                       JOIN devv5.concept c
+                            ON m.s_concept_code = c.concept_code
+                                AND c.vocabulary_id = 'CAP'
+                        JOIN devv5.concept cc
+                            ON m.target_concept_id = cc.concept_id
+                               AND cc.vocabulary_id =m.target_vocabulary_id
+              WHERE m.target_concept_id <> 0-- to exclude to 0 mappings
+)
+,
+      tabb AS (SELECT c.concept_code AS concept_code_1,
+                     cc.concept_code AS concept_code_2,
+                     c.vocabulary_id AS vocabulary_id_1,
+                     cc.vocabulary_id AS vocabulary_id_2,
+                     cr.relationship_id,
+                     TO_DATE('20200427', 'yyyymmdd') AS valid_start_date,
+                     TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
+                     NULL                            as invalid_reason
+              FROM cap_breast_mapping m
+                       JOIN devv5.concept c
+                            ON m.s_concept_code = c.concept_code
+                                AND c.vocabulary_id = 'CAP'
+                       JOIN devv5.concept_relationship cr
+ON m.target_concept_id=cr.concept_id_1
+AND cr.relationship_id='Maps to'
+AND cr.concept_id_2!=m.target_concept_id
+JOIN devv5.concept cc
+ON cr.concept_id_2=cc.concept_id
+AND m.target_vocabulary_id='Nebraska Lexicon'
+WHERE m.target_concept_id <> 0-- to exclude to 0 mappings
+)
+,
+tabc AS
+         (SELECT distinct * FROM taba
+UNION ALL
+SELECT distinct * FROM tabb)
+,
+     result_tab as (
+SELECT distinct * FROM tabc)
+,
+     unique_combo_check AS (select concept_code_1, concept_code_2, vocabulary_id_1, vocabulary_id_2, relationship_id
+                            from result_tab
+                            group by concept_code_1, concept_code_2, vocabulary_id_1, vocabulary_id_2, relationship_id
+                            having count(*) > 1
+     )
+
+SELECT distinct * FROM result_tab
+;
+
+-- relieves code without maps to only equivalents
+SELECT s_concept_code FROM cap_breast_mapping m
+     LEFT JOIN devv5.concept  c
+    ON m.target_concept_id=c.concept_id
+ LEFT JOIN devv5.concept_relationship cr
+ON c.concept_id=cr.concept_id_1
+AND cr.relationship_id='Maps to'
+AND cr.concept_id_2!=c.concept_id
+LEFT JOIN devv5.concept cc
+ON cr.concept_id_2=cc.concept_id
+AND m.target_vocabulary_id='Nebraska Lexicon'
+WHERE m.target_concept_id <> 0
+AND m.target_vocabulary_id='Nebraska Lexicon' AND m.standard_concept is null
+AND cc.concept_id is null
+;
+-- to prove that  these codes are the only
+with taba AS (SELECT c.concept_code AS concept_code_1,
+                     cc.concept_code AS concept_code_2,
+                     c.vocabulary_id AS vocabulary_id_1,
+                     cc.vocabulary_id  AS vocabulary_id_2,
+                     CASE
+                         WHEN cc.standard_concept = 'S'   then 'Maps to'
+                         ELSE concat('CAP', ' to ', coalesce(cc.vocabulary_id, 'NULL'), ' equivalent')
+                         END                         AS relationship_id,
+                     TO_DATE('20200427', 'yyyymmdd') AS valid_start_date,
+                     TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
+                     NULL                            as invalid_reason
+              FROM cap_breast_mapping m
+                       JOIN devv5.concept c
+                            ON m.s_concept_code = c.concept_code
+                                AND c.vocabulary_id = 'CAP'
+                        JOIN devv5.concept cc
+                            ON m.target_concept_id = cc.concept_id
+                               AND cc.vocabulary_id =m.target_vocabulary_id
+              WHERE m.target_concept_id <> 0-- to exclude to 0 mappings
+)
+,
+      tabb AS (SELECT c.concept_code AS concept_code_1,
+                     cc.concept_code AS concept_code_2,
+                     c.vocabulary_id AS vocabulary_id_1,
+                     cc.vocabulary_id AS vocabulary_id_2,
+                     cr.relationship_id,
+                     TO_DATE('20200427', 'yyyymmdd') AS valid_start_date,
+                     TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
+                     NULL                            as invalid_reason
+              FROM cap_breast_mapping m
+                       JOIN devv5.concept c
+                            ON m.s_concept_code = c.concept_code
+                                AND c.vocabulary_id = 'CAP'
+                       JOIN devv5.concept_relationship cr
+ON m.target_concept_id=cr.concept_id_1
+AND cr.relationship_id='Maps to'
+AND cr.concept_id_2!=m.target_concept_id
+JOIN devv5.concept cc
+ON cr.concept_id_2=cc.concept_id
+AND m.target_vocabulary_id='Nebraska Lexicon'
+WHERE m.target_concept_id <> 0-- to exclude to 0 mappings
+)
+,
+tabc AS
+         (SELECT distinct * FROM taba
+UNION ALL
+SELECT distinct * FROM tabb)
+
+SELECT distinct  concept_code_1 FROM tabc
+WHERE concept_code_1 IN (SELECT distinct  concept_code_1 FROM tabc
+GROUP BY concept_code_1 having count(distinct relationship_id)=1)
+AND relationship_id <>'Maps to'
+
+EXCEPT
+SELECT s_concept_code FROM cap_breast_mapping m
+     LEFT JOIN devv5.concept  c
+    ON m.target_concept_id=c.concept_id
+ LEFT JOIN devv5.concept_relationship cr
+ON c.concept_id=cr.concept_id_1
+AND cr.relationship_id='Maps to'
+AND cr.concept_id_2!=c.concept_id
+LEFT JOIN devv5.concept cc
+ON cr.concept_id_2=cc.concept_id
+AND m.target_vocabulary_id='Nebraska Lexicon'
+WHERE m.target_concept_id <> 0
+AND m.target_vocabulary_id='Nebraska Lexicon' AND m.standard_concept is null
+AND cc.concept_id is null
+;
+
+
+
 -- todo
 --  compare quantity and quality of NL and snomed attributes for same codes.
-SELECT distinct c.concept_name,c.concept_class_id, relationship_id,cc.concept_class_id, count(*)
-FROM devv5.concept c
-JOIN   devv5.concept_relationship cr
-ON c.concept_id=cr.concept_id_1
-AND c.vocabulary_id='Nebraska Lexicon'
-JOIN   devv5.concept  cc
-ON cc.concept_id=cr.concept_id_2
-AND cc.vocabulary_id ='Nebraska Lexicon'
-WHERE c.concept_code IN (SELECT concept_code FROM cap_breast_mapping WHERE target_vocabulary_id ='Nebraska Lexicon')
-GROUP BY  c.concept_name,c.concept_class_id, relationship_id,cc.concept_class_id
-ORDER BY c.concept_name
-;
-
-SELECT distinct c.concept_name,c.concept_class_id, relationship_id,cc.concept_class_id, count(*)
-FROM devv5.concept c
-JOIN   devv5.concept_relationship cr
-ON c.concept_id=cr.concept_id_1
-AND c.vocabulary_id='SNOMED'
-JOIN   devv5.concept  cc
-ON cc.concept_id=cr.concept_id_2
-AND cc.vocabulary_id ='SNOMED'
-WHERE c.concept_code IN (SELECT concept_code FROM cap_breast_mapping WHERE target_vocabulary_id ='Nebraska Lexicon')
-AND cr.relationship_id !~* 'map'
-GROUP BY  c.concept_name,c.concept_class_id, relationship_id,cc.concept_class_id
-ORDER BY c.concept_name
-;
-
-SELECT distinct standard_concept from cap_breast_mapping
-
-
--- CRM preparation
-SELECT m.s_concept_code,
-       m.concept_code,
-       m.s_vocabulary_id,
-       m.target_vocabulary_id,
-       CASE WHEN  (m.target_vocabulary_id = 'SNOMED'  or (m.target_vocabulary_id = 'Nebraska Lexicon' AND m.standard_concept  in ('S','Standard') ) )then 'Maps to'
-            ELSE concat('CAP', ' to ', coalesce(m.target_vocabulary_id,'NULL'),  ' equivalent')
-             END AS relationship_id,
-       TO_DATE('20200418', 'yyyymmdd') AS valid_start_date ,
-       TO_DATE('20991231', 'yyyymmdd') AS valid_end_date,
-      NULL as invalid_reason
-FROM cap_breast_mapping m
-JOIN devv5.concept c
-ON m.s_concept_code=c.concept_code
-ANd c.vocabulary_id='CAP'
-WHERE m.target_concept_id <>0 -- to exlude to 0 mappings
-;+WITH tabN AS (
+    SELECT distinct c.concept_code, c.concept_name, c.concept_class_id, relationship_id, cc.concept_class_id, count(*)
+    FROM devv5.concept c
+             JOIN devv5.concept_relationship cr
+                  ON c.concept_id = cr.concept_id_1
+                      AND c.vocabulary_id = 'Nebraska Lexicon'
+             JOIN devv5.concept cc
+                  ON cc.concept_id = cr.concept_id_2
+                      AND cc.vocabulary_id = 'Nebraska Lexicon'
+    WHERE c.concept_code IN
+          (SELECT concept_code FROM cap_breast_mapping WHERE target_vocabulary_id = 'Nebraska Lexicon')
+      AND cr.relationship_id !~* 'map'
+    GROUP BY c.concept_code, c.concept_name, c.concept_class_id, relationship_id, cc.concept_class_id
+    ORDER BY c.concept_name
+)
+, tabS AS (
+    SELECT distinct c.concept_code, c.concept_name, c.concept_class_id, relationship_id, cc.concept_class_id, count(*)
+    FROM devv5.concept c
+             JOIN devv5.concept_relationship cr
+                  ON c.concept_id = cr.concept_id_1
+                      AND c.vocabulary_id = 'SNOMED'
+             JOIN devv5.concept cc
+                  ON cc.concept_id = cr.concept_id_2
+                      AND cc.vocabulary_id = 'SNOMED'
+    WHERE c.concept_code IN
+          (SELECT concept_code FROM cap_breast_mapping WHERE target_vocabulary_id = 'Nebraska Lexicon')
+      AND cr.relationship_id !~* 'map'
+    GROUP BY c.concept_code, c.concept_name, c.concept_class_id, relationship_id, cc.concept_class_id
+    ORDER BY c.concept_name
+)
+;
+
+

on a.s_concept_code=split_part(b.Item_Ckey,'.',1)
AND a.concept_code != b.ConceptID
;

-- DROP TABLE cap_breast_mapping;
CREATE UNLOGGED TABLE cap_breast_mapping
(

    GROUP BY s_concept_code
    HAVING count (*) > 1)
ORDER BY s_concept_code
;

-- Not perfect mapping of CAP Variables
