MID Validation

select a.* from atoms a, concepts_atoms b, concepts c
where a.id = b.atoms_id and b.concepts_id = c.id ...;

select sty.* from concepts a, concepts_semantic_type_components b, semantic_type_components sty
where a.id = b.concepts_id and b.semanticTypes_id = sty.id ...;

select b.alternateTerminologyIds aui
from atoms a, AtomJpa_alternateTerminologyIds b
where a.id = b.AtomJpa_id and alternateTerminologyIds_KEY = :terminology ...;

select a.alternateTerminologyIds srcAtomId
from atoms a, AtomJpa_alternateTerminologyIds b
where a.id = b.AtomJpa_id and alternateTerminologyIds_KEY = concat(:terminology,'-SRC') ...;

select a.conceptTerminologyIds srcAtomId
from atoms a, AtomJpa_conceptTerminologyIds b
where a.id = b.AtomJpa_id and conceptTerminologyIds_KEY = :terminology ...;

select c.terminologyId cui from concepts c where terminology = :terminology ...;

select sty.terminologyId atui from semantic_type_components where terminology = :terminology...;

select r.terminologyId rui from concept_relationships r where terminology = :terminology ...;

select r.alternateTerminologyIds rui
from concept_relationships r, ConceptRelationshipJpa_alternateTerminologyIds b
where r.id = b.ConceptRelationshipJpa_id and alternateTerminologyIds_KEY = :terminology ...;

-- default precedence list
select a.id, sab.terminologies, tty.termTypes
from precedence_lists a, precedence_list_terminologies sab, precedence_list_term_types tty
where sab.terminologies_ORDER = tty.termTypes_ORDER
  and a.id = sab.PrecedenceListJpa_id and a.id = tty.PrecedenceListJpa_id
  and a.terminology = :terminology;
 
-- project precedence list
select a.id, sab.terminologies, tty.termTypes
from precedence_lists a, precedence_list_terminologies sab, precedence_list_term_types tty
where sab.terminologies_ORDER = tty.termTypes_ORDER
  and a.id = sab.PrecedenceListJpa_id and a.id = tty.PrecedenceListJpa_id
  and a.id in (select precedenceList_id from projects where terminology = :terminology);

select * from additional_relationship_types....

select cr.from_id conceptId1, cr.to_id conceptId2
from atom_relationships ar, concept_relationships cr,
     concepts_atoms ca1, concepts_atoms ca2
where ar.terminology = :terminology and cr.terminology = :terminology
  and ar.from_id = ca1.atoms_id and ar.to_id = ca2.atoms_id
  and cr.from_id = ca1.concepts_id and cr.to_id = ca2.concepts_id
  and ar.workflowStatus = 'DEMOTION'
  and cr.workflowStatus in ('READY_FOR_PUBLICATION','PUBLISHED');

select c.id, a.id conceptId from concepts c, concepts_atoms ca,
    (select id from atoms where name like ' %' or name like '% ') a
where c.terminology=:terminology and c.id = ca.concepts_id
and ca.atoms_id = a.id;

select c.id conceptId, a.id
from concepts c, concepts_atoms ca,
    (select id from atoms where language='') a
where ca.atoms_id = a.id and ca.concepts_id = c.id
and c.terminology = :terminology;

select codeId, id as atomId, terminology from atoms a where codeId like ' %' or codeId like '% '

select terminology,version
from terminologies b
where current = true
 and not exists
 (select * from atoms a
 where a.terminology = b.terminology
 and a.version=b.version
 and a.publishable = true);

-- concept_relationships
select at.id as attribute_id, c.id as concept_id, c.terminology from
concept_relationships c, concept_relationships_attributes c_at, attributes at
where c.id = c_at.concept_relationships_id and at.id = c_at.attributes_id
and (c.terminology,c.version) in (select terminology,version from terminologies where current = false)
and (at.terminology,at.version) in (select terminology,version from terminologies where current = true)
and at.publishable = true;

select a_r.id, a_r.from_id, a.terminology, a_r.to_id
from atoms a, atom_relationships a_r
where a.id = a_r.from_id
and (a.terminology,a.version) in (select terminology,version from terminologies where current = false)
and (a_r.terminology,a_r.version) in (select terminology,version from terminologies where current = true)

select a.id, a.name
from mapsets a
where a.publishable = true
  and not exists
   (select * from mappings b where a.id = b.mapset_id)

select a.id, a.name
from atom_subsets a
where a.publishable = true
  and not exists
   (select * from atom_subset_members b where a.id = b.subset_id)
union all
select a.id, a.name
from concept_subsets a
where a.publishable = true
  and not exists
   (select * from concept_subset_members b where a.id = b.subset_id);

select inverse_id from relationship_types group by inverse_id having count(*)>1;

select inverse_id from additional_relationship_types group by inverse_id having count(*)>1;

select a.id from concept_relationships a
where terminology = :terminology
  and relationshipType not in ('XR','RO','RB','RN','BRO','BRB','BRN');

select a.id from concept_relationships a
where terminology != :terminology
  and workflowStatus = 'NEEDS_REVIEW'
union all
select a.id from atom_relationships a
where workflowStatus = 'NEEDS_REVIEW'
union all
select a.id from code_relationships a
where workflowStatus = 'NEEDS_REVIEW'
union all
select a.id from descriptor_relationships a
where workflowStatus = 'NEEDS_REVIEW';

select r.to_id conceptId1, r.from_id conceptId2
from concept_relationships r
where terminology = :terminology
group by r.to_id, r.from_id having count(*)>1;

select r.from_id conceptId
from concept_relationships r
where terminology = :terminology
  and from_id = to_id;

select distinct a.terminology
from atoms a, term_types b
where a.termType = b.abbreviation
  and a.suppressible = true and b.suppressible = false
  and a.terminology not in ('CSP','CST','DXP','FMA','MTH','NCBI','RXNORM','UWDA'); 

select distinct a.terminology
from atoms a
where a.obsolete = true
  and a.terminology not in ('CVX', 'FMA', 'GO','HCPCS','HPO','HL7V3.0','LNC','MDR','MGED','MVX','MTHSPL','NCI','NICHD','PDQ','RXNORM','VANDF'); 

select distinct c1.id conceptId
from atoms a1, concepts_atoms b1, concepts c1, 
     atoms a2, concepts_atoms b2, concepts c2
where a1.id = b1.atoms_id and b1.concepts_id = c1.id
  and a2.id = b2.atoms_id and b2.concepts_id = c2.id
  and c1.terminology = :terminology
  and c2.terminology = :terminology
  and c1.id = c2.id
  and a1.publishable = true
  and a1.terminology = 'SRC' and a1.termType = 'VPT'
  and a2.publishable = true
  and a2.terminology = 'SRC' AND a2.termType not in ('VAB','VPT','VSY');

select c1.id conceptId
from atoms a1, concepts_atoms b1, concepts c1, 
     atoms a2, concepts_atoms b2, concepts c2
where a1.id = b1.atoms_id and b1.concepts_id = c1.id
  and a2.id = b2.atoms_id and b2.concepts_id = c2.id
  and c1.terminology = :terminology
  and c2.terminology = :terminology
  and c1.id = c2.id
  and a1.terminology = 'SRC'
  and a1.termType = 'RAB'
  and a2.terminology = 'SRC'
  and a2.code not like concat('V-',a1.codeId);

select c1.id conceptId
from atoms a1, concepts_atoms b1, concepts c1, 
     atoms a2, concepts_atoms b2, concepts c2
where a1.id = b1.atoms_id and b1.concepts_id = c1.id
  and a2.id = b2.atoms_id and b2.concepts_id = c2.id
  and c1.terminology = :terminology
  and c2.terminology = :terminology
  and c1.id = c2.id
  and a1.terminology = 'SRC'
  and a1.termType = 'VAB'
  and a2.terminology = 'SRC'
  and a2.code not like concat('V-',a1.codeId);

select concepts_id as conceptId from concepts_atoms group by conceptId, atoms_id having count(*) >1;

SELECT from_id AS conceptId1, to_id AS conceptId2
FROM concept_relationships
WHERE terminology = 'SRC'AND additionalRelationshipType = 'version_of'AND publishable = TRUE;

SELECT c1.id as conceptId1, c2.id as conceptId2 
FROM concepts c1, concepts c2, descriptor_relationships b, descriptors_atoms da1, descriptors_atoms da2, concepts_atoms ca1, concepts_atoms ca2 
WHERE b.from_id = da1.descriptors_id AND b.to_id = da2.descriptors_id 
AND da1.atoms_id = ca1.atoms_id AND da2.atoms_id = ca2.atoms_id 
AND c1.id = ca1.concepts_id AND c2.id = ca2.concepts_id 
AND c1.terminology = 'NCIMTH' AND c2.terminology = 'NCIMTH' AND
b.terminology='SRC' AND b.additionalRelationshipType='version_of' AND b.publishable=true;


#ATOM RELATIONSHIPS ARE ALLOWED TO HAVE THIS CONDITION. QUERY NOT IMPLEMENTED

SELECT ca1.concepts_id as conceptId1, ca2.concepts_id as conceptId2 
FROM atom_relationships ar,concepts_atoms ca1, concepts_atoms ca2
WHERE ar.from_id = ca1.atoms_id
AND ar.to_id = ca2.atoms_id 
AND ar.terminology='SRC'
AND ar.additionalRelationshipType = 'version_of' 
AND ar.publishable=true;

select terminology
from terminologies t where current = true
and not exists
 (select * from atoms a
  where a.terminology = 'SRC' and a.publishable = true
    and a.termType = 'VAB' and a.codeId = concat('V-',t.terminology));

how to find treetop?

select id from code_relationships where terminology!='SRC' and additionalRelationshipType='version_of' and publishable=true;

SELECT c_a.concepts_id FROM atoms a, concepts_atoms c_a
WHERE a.publishable = false
    AND a.terminology = 'SRC' AND a.termType = 'VAB' 
    AND a.id = c_a.atoms_id
    AND SUBSTRING(a.codeId,3) IN
    (SELECT concat(terminology,'_',version)
     FROM terminologies t WHERE current = false)
AND NOT EXISTS
(SELECT * FROM concept_relationships r, concepts_atoms c_a2, atoms a2
 WHERE a2.publishable=true
 AND a2.terminology='SRC' AND a2.termType='RAB' 
 AND a2.id = c_a2.atoms_id
 AND c_a.concepts_id = r.from_id
 AND c_a2.concepts_id = r.to_id
 AND r.relationshipType='BRO'
);

SELECT c_a.concepts_id, SUBSTRING(a.codeId,3) obsolete_source FROM atoms a, concepts_atoms c_a
WHERE a.publishable = true
    AND a.terminology = 'SRC' AND a.termType = 'VAB'
    AND a.id = c_a.atoms_id
    AND SUBSTRING(a.codeId,3) IN
    (SELECT concat(terminology,'_',version)
     FROM terminologies t WHERE current = false);

--official name is terminology.preferredName
SELECT c_a.concepts_id, a.name
FROM atoms a, concepts_atoms c_a
WHERE a.publishable=true
AND a.terminology='SRC' AND a.termType='VPT' 
AND a.id = c_a.atoms_id
AND NOT EXISTS
    (SELECT * from terminologies t
     where t.preferredName=a.name);

SELECT * FROM atoms a
WHERE a.terminology='SRC' AND a.termType='RPT' AND a.publishable=true
AND NOT EXISTS
   (SELECT * FROM atoms b
    WHERE b.terminology=SUBSTRING(a.codeId,3) AND b.publishable=true);

SELECT c.id FROM atoms a, concepts_atoms c_a, concepts c
WHERE a.id = c_a.atoms_id AND c_a.concepts_id = c.id
AND a.terminology='SRC' AND a.publishable=true
AND NOT EXISTS
    (select sty.* from concepts_semantic_type_components cstc, semantic_type_components sty
     where c.id = cstc.concepts_id and cstc.semanticTypes_id = sty.id
     and sty.semanticType='Intellectual Product');

atom/code relationships connected to SRC atoms or codes must be owned by SRC.

no atom_tree_positions or concept_tree_positions or code/descriptor tree positions

there is a source family value in root_terminologies that does not match the abbreviation of a root terminology that has a current terminology version

select distinct a.id conceptId
from attributes a
where suppressible = true
and terminology not in ('VANDF','MTHSPL');

select a.from_id as conceptId from atom_relationships a
where a.suppressible = true
union
select a.from_id from concept_relationships a
where a.suppressible = true
  and a.terminology != 'MTH'
union
select a.from_id from code_relationships a
where a.suppressible = true
union
select a.from_id from descriptor_relationships a
where a.suppressible = true;

select terminology,version from atoms
where (terminology,version) in (select terminology,version from terminologies where current = false)
group by terminology having max(lastPublishedRank) = '0'; 

select distinct terminology as conceptId
from atoms
where codeId = 'NOCODE' 
and terminology not in 
('COSTAR','DXP','MCM','MTH','MTHCMSFRF','MTHMST','MTHSPL','NCIMTH');

#Original Query Below
/* select distinct terminology,version
from atoms 
where (terminology,version) in (select terminology,version from terminologies where current = false)
 and codeId = 'NOCODE'; */

select id conceptId
from concepts
where terminology = :terminology
  and terminologyId != id
  and terminologyId not like 'C_______' and terminologyId not like 'CL_______';

select a.from_id conceptId1, a.to_id conceptId2
from concept_relationships a
where terminology = :terminology
  and ((relationshipType in ('XR','BRB','BRN','BRO') and publishable = true) OR 
       (relationshipType not in ('XR','BRB','BRN','BRO') AND publishable  = false));

-- atom.tty
select termType from atoms a where not exists (select * from term_types b where a.termType = b.abbreviation)
and a.publishable = true;

-- attribute.name
select name from attributes a where not exists (select * from attribute_names b where a.name = b.abbreviation)
and a.publishable = true;

-- relationship.rel
select relationshipType from concept_relationships a 
where not exists (select * from relationship_types b where a.relationshipType= b.abbreviation) 
and a.publishable = true
union
select relationshipType from atom_relationships a 
where not exists (select * from relationship_types b where a.relationshipType= b.abbreviation) 
and a.publishable = true
union
select relationshipType from code_relationships a 
where not exists (select * from relationship_types b where a.relationshipType= b.abbreviation) 
and a.publishable = true
union
select relationshipType from descriptor_relationships a 
where not exists (select * from relationship_types b where a.relationshipType= b.abbreviation) 
and a.publishable = true;

-- relationship.rela
-- same as above but "additionalRelationshipType" instead of "relationshipType"
-- also include "atom_tree_positions", "concept_tree_positions", ...

-- atom.terminology,tty
select terminology, termType from atoms a 
where not exists 
  (select * from precedence_lists b, precedence_list_terminologies c, precedence_list_termtypes d
   where b.id = c.PrecedenceListJpa_id and b.id = d.PrecedenceListJpa_id
     and b.terminology = 'NCIMTH'
     and a.terminology = c.terminologies and a.termType = d.termTypes
     and c.terminologies_ORDER = d.termTypes_ORDER)
and a.publishable = true;

select terminology from
(
select terminology from atoms where publishable = true
union
select terminology from codes where publishable = true
union
select terminology from descriptors where publishable = true
union
select terminology from concepts where publishable = true
union
select terminology from atom_relationships where publishable = true
union
select terminology from concept_relationships where publishable = true
union
select terminology from code_relationships where publishable = true
union
select terminology from descriptor_relationships where publishable = true
union
select terminology from atom_subset_members where publishable = true
union
select terminology from atom_subsets where publishable = true
union
select terminology from atom_transitive_rels where publishable = true
union
select terminology from atom_tree_positions where publishable = true
union
select terminology from attribute_identity
union
select terminology from attribute_names where publishable = true
union
select terminology from attributes where publishable = true
union
select terminology from code_transitive_rels where publishable = true
union
select terminology from code_tree_positions where publishable = true
union
select terminology from component_histories where publishable = true
union
select terminology from component_info_relationships where publishable = true
union
select terminology from concept_subset_members where publishable = true
union
select terminology from concept_subsets where publishable = true
union
select terminology from concept_transitive_rels where publishable = true
union
select terminology from concept_tree_positions where publishable = true
union
select terminology from deep_atom_relationships where publishable = true
union
select terminology from deep_concept_relationships where publishable = true
union
select terminology from definitions where publishable = true
union
select terminology from descriptor_tree_positions where publishable = true
union
select terminology from general_concept_axioms where publishable = true
union
select terminology from general_metadata_entries where publishable = true
union
select terminology from label_sets where publishable = true
union
select terminology from languages where publishable = true
union
select terminology from lexical_classes where publishable = true
union
select terminology from mappings where publishable = true
union
select terminology from mapsets where publishable = true
union
select terminology from relationship_types where publishable = true
union
select terminology from semantic_type_components where publishable = true
union
select terminology from semantic_types where publishable = true
union
select terminology from string_classes where publishable = true
union
select terminology from term_types where publishable = true
) a
where  not exists (select * from terminologies b where a.terminology = b.terminology);

select id from atoms where language != 'ENG';

select id,semanticType from semantic_type_components 
where semanticType not in
(select expandedForm from semantic_types);

select abbreviation, inverse_id from additional_relationship_types where inverse_id is null
union
select b.abbreviation, b.id from additional_relationship_types a, additional_relationship_types b, additional_relationship_types c
where a.inverse_id = b.id and b.inverse_id = c.id and a.id != c.id and a.publishable = true and c.publishable = true;

select abbreviation, inverse_id from relationship_types where inverse_id is null
union
select b.abbreviation, b.id from relationship_types a, relationship_types b, relationship_types c
where a.inverse_id = b.id and b.inverse_id = c.id and a.id != c.id and a.publishable = true and c.publishable = true;

-- play around with "1~2~3~4" and try to get '4' and '1~2~3'

select a.ancestorPath
from atom_tree_positions a, atoms where a.node_id = atoms.id and not exists
  (select * from atom_tree_positions b where SUBSTRING_INDEX(a.ancestorPath, '~', -1) = atoms.terminologyId
   and a.ancestorPath like '%~%'
   and LEFT(a.ancestorPath, LENGTH(a.ancestorPath) - LENGTH(SUBSTRING_INDEX(a.ancestorPath, '~', -1)) - 1) = b.ancestorPath
   and a.terminology = b.terminology
   and a.additionalRelationshipType = b.additionalRelationshipType)

-- repeat for concept_tree_positions, code_tree_positions, descriptor_tree_positions

-- NOTE: we may already have a check like this, so it could be redundant
--   check first
select c1.id as conceptId1, c2.id as conceptId2
from concept_relationships cr, concepts c1, concepts c2
where cr.from_id = c1.id and cr.to_id = c2.id
AND from_id<to_id
and cr.terminology='NCIMTH'
and c1.terminology='NCIMTH' and c2.terminology='NCIMTH'
and cr.publishable = true and c1.publishable = true and c2.publishable = true
GROUP BY cui1,cui2 HAVING COUNT(*)>1;

-- NOTE: this may already exist, check first  (OR see the fix for mth-only concepts, it likely has a query to find this)
-- see query action process -> turn of MTH/PN-only concepts
select c1.id as conceptId
from concepts c, concepts_atoms ca, atoms a
where c.id = ca.concepts_id and ca.atoms_id = a.id
and c.publishable = true
and a.publishable = true
and (a.terminology = 'MTH' OR a.terminology = 'NCIMTH')
and not exists
  (select * from concepts_atoms ca2, atoms a2
   where ca2.atoms_id = a2.id
     and a2.publishable = true
     and ca1.concepts_id = ca2.concepts_id
     and a.terminology != 'MTH' and a.terminology != 'NCIMTH');

-- "duplicate" RUI
select id from relationship_identity
group by other fields,... having count(*)>1;

-- duplicate ATUI
... same thing from attribute_identity ...

-- same for AUI - atom_identity
-- same for LUI - lexical_class_identity
-- same for SUI - string_class_identity


-- NOT for CUI

-- NOTE: if the atoms stringClassId starts with an S you have to remove it
select a.stringClassId, a.name atoms_name, s_c_i.name sci_name
from atoms a, string_class_identity s_c_i
where (substr(a.stringClassId,2))+0) = s_c_i.id and a.name != s_c_i.name;

select id from atoms where terminology != 'SRC'
and (name,(substr(stringClassId,2))+0) not in
    (select name,id from string_class_identity);

select id from atoms where terminology != 'SRC'
and (substr(lexicalClassId,2)+0) not in
    (select id from lexical_class_identity);

select id from atoms a where not exists (select * from string_class_identity b where b.id = (substr(a.stringClassId,2))+0) );
select id from atoms a where not exists (select * from lexical_class_identity b where b.id = (substr(a.lexicalClassId,2))+0) );

-- leading, trailing or duplicate whitespace in names (if there are duplicate whitespace things, just remove that part)
select id from atoms where (name like ' %' OR name like '% ' OR name like '%  %') and publishable = true; 

-- check for \t, \r, or \n (look up ascii numbers for these 3 chars)
SELECT * FROM atoms WHERE INSTR(name, char(10)) > 0 and publishable = true
union
SELECT * FROM atoms WHERE INSTR(name, char(09)) > 0 and publishable = true
union
SELECT * FROM atoms WHERE INSTR(name, char(13)) > 0 and publishable = true;

select * from atomjpa_alternateterminologyids where alternateTerminologyIds_KEY = 'NCIMTH'
  AND not exists (select * from atom_identity b where b.id = (substr(a.alternateTerminologyIds,2)+0) );

select * from atomrelationshipjpa_alternateterminologyids a where alternateTerminologyIds_KEY = 'NCIMTH'
  AND not exists (select * from relationship_identity b where b.id = (substr(a.alternateTerminologyIds,2)+0) )
UNION ALL
select * from coderelationshipjpa_alternateterminologyids a where alternateTerminologyIds_KEY = 'NCIMTH'
  AND not exists (select * from relationship_identity b where b.id = (substr(a.alternateTerminologyIds,2)+0) )
UNION ALL
select * from componentinforelationshipjpa_alternateterminologyids a where alternateTerminologyIds_KEY = 'NCIMTH'
  AND not exists (select * from relationship_identity b where b.id = (substr(a.alternateTerminologyIds,2)+0) )
UNION ALL
select * from conceptrelationshipjpa_alternateterminologyids a where alternateTerminologyIds_KEY = 'NCIMTH'
  AND not exists (select * from relationship_identity b where b.id = (substr(a.alternateTerminologyIds,2)+0) )
UNION ALL
select * from descriptorrelationshipjpa_alternateterminologyids a where alternateTerminologyIds_KEY = 'NCIMTH'
  AND not exists (select * from relationship_identity b where b.id = (substr(a.alternateTerminologyIds,2)+0) )

select alternateTerminologyIds from atomjpa_alternateterminologyids where alternateTerminologyIds_KEY = 'NCIMTH-SRC' group by alternateTerminologyIds having count(*)>1;

-- if above has exceptions try
select alternateTerminologyIds from atomjpa_alternateterminologyids where alternateTerminologyIds_KEY = 'NCIMTH-SRC' group by alternateTerminologyIds having count(distinct AtomJpa_id)>1;

-- ADD a unique index to relationship_identity inverseId

select * from atomrelationshipjpa_alternatetermiologyids a, atomrelationshipjpa_alternatetermiologyids b, relationship_identity c
where a.alternateTerminologyIds_KEY  = 'NCIMTH' and b.alternateTerminologyIds_KEY  = 'NCIMTH'
  and (substr(a.alternateTerminologyIds,2))+0) ) = c.id
  and (substr(b.alternateTerminologyIds,2))+0) ) = c.inverseId
group by b.alternateTerminologyIds having count(distinct a.alternateTerminologyIds)>1;

... do this also for the other relationship alternate id tables.

-- ADD a unique index to relationship_identity inverseId

select * from atomrelationshipjpa_alternatetermiologyids a
where a.alternateTerminologyIds_KEY  = 'NCIMTH'
  and not exists
  (select * from atomrelationshipjpa_alternatetermiologyids b, relationship_identity c
   where b.alternateTerminologyIds_KEY  = 'NCIMTH'
     and (substr(a.alternateTerminologyIds,2))+0) ) = c.id
     and (substr(b.alternateTerminologyIds,2))+0) ) = c.inverseId);

... do this also for the other relationship alternate id tables.

select id from tracking_records tr 
left join checklists_tracking_records ctr on tr.id=ctr.trackingRecords_id 
left join worklists_tracking_records wtr on tr.id=wtr.trackingRecords_id 
left join workflow_bins_tracking_records wbtr on tr.id=wbtr.trackingRecords_id 
where ctr.trackingRecords_id is null 
and wtr.trackingRecords_id is null 
and wbtr.trackingRecords_id is null;

SELECT id FROM atoms a 
WHERE NOT EXISTS
    (SELECT * from atom_identity a_i
     WHERE
     a.codeId = a_i.codeId AND 
     a.conceptId = a_i.conceptId AND
     a.descriptorId = a_i.descriptorId AND
     a.stringClassId = a_i.stringClassId AND
     a.termType = a_i.termType AND
     a.terminology = a_i.terminology AND
     a.terminologyId = a_i.terminologyId
    );
    
SELECT id FROM atoms a
WHERE NOT EXISTS
    (SELECT alternateTerminologyIds from AtomJpa_alternateTerminologyIds b
     WHERE a.id = b.AtomJpa_id and alternateTerminologyIds_KEY = :terminology);

SELECT id FROM atom_relationships r
WHERE NOT EXISTS
    (SELECT alternateTerminologyIds FROM AtomRelationshipJpa_alternateTerminologyIds b
     WHERE r.id = b.AtomRelationshipJpa_id AND alternateTerminologyIds_KEY != :terminology);
     
-- does not apply for concepts because NCIMTH

SELECT id FROM code_relationships r
WHERE NOT EXISTS
    (SELECT alternateTerminologyIds FROM CodeRelationshipJpa_alternateTerminologyIds b
     WHERE r.id = b.CodeRelationshipJpa_id AND alternateTerminologyIds_KEY != :terminology);
     
SELECT id FROM descriptor_relationships r
WHERE NOT EXISTS
    (SELECT alternateTerminologyIds FROM DescriptorRelationshipJpa_alternateTerminologyIds b
     WHERE r.id = b.DescriptorRelationshipJpa_id AND alternateTerminologyIds_KEY != :terminology);
     
SELECT id FROM component_info_relationships r
WHERE NOT EXISTS
    (SELECT alternateTerminologyIds FROM ComponentInfoRelationshipJpa_alternateTerminologyIds b
     WHERE r.id = b.ComponentInfoRelationshipJpa_id AND alternateTerminologyIds_KEY != :terminology);

SELECT a.id FROM attributes a, atoms_attributes a_a, atoms b, AtomJpa_alternateTerminologyIds b_a
WHERE a.id = a_a.attributes_id and b.id = a_a.atoms_id
AND b.id = b_a.AtomJpa_id AND b_a.alternateTerminologyIds_KEY = :terminology
AND (b_a.alternateTerminologyIds,b.terminology,'ATOM',a.name,a.terminology, a.terminologyId)
NOT IN
    (SELECT componentId,componentTerminology, componentType, name, terminology, terminologyId
     FROM attribute_identity a_i
     WHERE
     a_i.componentType = 'ATOM');
     
SELECT a.id FROM attributes a, codes_attributes c_a, codes b
WHERE a.id = c_a.attributes_id and b.id = c_a.codes_id
AND (b.id, b.terminology,'CODE',a.name,a.terminology, a.terminologyId)
NOT IN
    (SELECT componentId, componentTerminology, componentType, name, terminology, terminologyId
     FROM attribute_identity a_i
     WHERE
     a_i.componentType = 'CODE');
     
SELECT a.id FROM attributes a, concepts_attributes c_a, concepts b
WHERE a.id = c_a.attributes_id and b.id = c_a.concepts_id
AND (b.id,b.terminology,'CONCEPT',a.name,a.terminology, a.terminologyId)
NOT IN
    (SELECT componentId, componentTerminology, componentType, name, terminology, terminologyId
     FROM attribute_identity a_i
     WHERE
     a_i.componentType = 'CONCEPT');
    
SELECT a.id FROM attributes a, descriptors_attributes d_a, concepts b
WHERE a.id = d_a.attributes_id and b.id = d_a.descriptors_id
AND (b.id,b.terminology,'DESCRIPTOR',a.name,a.terminology, a.terminologyId)
NOT IN
    (SELECT componentId,componentTerminology, componentType, name, terminology, terminologyId
     FROM attribute_identity a_i
     WHERE
     a_i.componentType = 'DESCRIPTOR');

-- Invalid inverseId in relationship_identity

select id,inverseId from relationship_identity
WHERE inverseId is null 
OR inverseId NOT IN
    (select id from relationship_identity);

-- Invalid inverse_id values in relationship_identity

SELECT id FROM relationship_identity WHERE NOT EXISTS
(SELECT ri1.id, ri2.id
FROM relationship_identity ri1, relationship_identity ri2
WHERE ri1.inverseId=ri2.id
AND ri1.additionalRelationshipType=
    (SELECT art1.abbreviation
     FROM additional_relationship_types art1, additional_relationship_types art2
     WHERE ri1.additionalRelationshipType = art1.abbreviation
       AND art1.terminology = :terminology
       AND art1.inverse_id = art2.id
       AND art2.terminology = :terminology
       AND art2.abbreviation = ri2.additionalRelationshipType
     )
AND ri1.terminology = ri2.terminology AND ri1.terminologyId=ri2.terminologyId
AND ri1.fromId = ri2.toId AND ri1.fromTerminology = ri2.toTerminology AND ri1.fromType = ri2.toType
AND ri1.relationshipType =
    (SELECT rt1.abbreviation
     FROM relationship_types rt1, relationship_types rt2
     WHERE ri1.relationshipType = rt1.abbreviation 
       AND rt1.terminology = :terminology
       AND rt1.inverse_id=rt2.id
       AND rt2.terminology = :terminology
       AND rt2.abbreviation = ri2.relationshipType
     )
AND ri1.terminology = ri2.terminology AND ri1.terminologyId=ri2.terminologyId
AND ri1.toId = ri2.fromId AND ri1.toTerminology=ri2.fromTerminology AND ri1.toType = ri2.fromType
);

select id from definitions d
WHERE publishable=true
AND NOT EXISTS
(SELECT * FROM
    (SELECT definitions_id FROM atoms_definitions
     UNION ALL
     SELECT definitions_id FROM concepts_definitions
     UNION ALL
     SELECT definitions_id FROM descriptors_definitions) a
 WHERE d.id = a.definitions_id);