combos

--1. ATC 4th
  --1.1 ATC 4th, and
  select
    c1.concept_id as atc5_id, c1.concept_name as atc5_name,
    c2.concept_id as atc4_id, c2.concept_name as atc4_name,
    c4.concept_id as rxnorm_id, c4.concept_name as rxnorm_name
  from concept c1
  join concept_relationship r1 on r1.concept_id_1=c1.concept_id and r1.invalid_reason is null and r1.relationship_id='Is a'
  join concept c2 on r1.concept_id_2=c2.concept_id
  join concept_relationship r2 on r2.concept_id_1=c2.concept_id and r2.invalid_reason is null and r2.relationship_id='Subsumes'
  join concept c3 on c3.concept_id = r2.concept_id_2
  join relationship_to_concept rtc on rtc.concept_code_1 = c3.concept_name
  join concept c4 on c4.concept_id = rtc.concept_id_2
  where c1.concept_class_id='ATC 5th'  and c1.vocabulary_id  = 'ATC'
  and lower(c1.concept_name) ~ ' and '
  and not c2.concept_name ~* ' and |comb| with'-- take narrow ATC4
  and not c3.concept_name ~* ' and |comb| with'
  order by c2.concept_code;

  --1.2 ATC 3rd, and
  select
   -- c1.concept_id as atc5_id, c1.concept_name as atc5_name,
    c3.concept_id as atc3_id, c3.concept_name as atc3_name,
    c6.concept_id as rxnorm_id, c6.concept_name as rxnorm_name
  from concept c1
  join concept_relationship r1 on r1.concept_id_1=c1.concept_id and r1.invalid_reason is null and r1.relationship_id='Is a'
  join concept c2 on r1.concept_id_2=c2.concept_id
  join concept_relationship r2 on r2.concept_id_1=c2.concept_id and r2.invalid_reason is null and r2.relationship_id='Is a'
  join concept c3 on r2.concept_id_2=c3.concept_id
  join concept c4 on regexp_replace(c4.concept_code, '.$', '') = c3.concept_code -- siblings
  join concept_relationship r3 on r3.concept_id_1=c4.concept_id and r3.invalid_reason is null and r3.relationship_id='Subsumes' --their descendants
  join concept c5 on r3.concept_id_2=c5.concept_id
  join relationship_to_concept rtc on rtc.concept_code_1 = c6.concept_name -- no need to exclude anything, will join only ingredients
  join concept c6 on c6.concept_id = rtc.concept_id_2
  where c1.concept_class_id='ATC 5th'  and c1.vocabulary_id  = 'ATC' and lower(c1.concept_name) ~ ' and '
  and c2.concept_name ~* ' and |comb| with'
  and not c3.concept_name ~* ' and |comb| with' -- take narrow ATC3
  and not c4.concept_name ~* ' and |comb| with' -- only pure siblings
  and c5.vocabulary_id  = 'ATC'
  order by c1.concept_code
  ;

  --1.3 ATC 2rd, and [there are cases where the first ATC group is good and where many are good]
  select distinct
   -- c1.concept_id as atc5_id, c1.concept_name as atc5_name,
    c4.concept_id as atc2_name, c4.concept_name as atc2_name,
    c8.concept_id as rxnorm_id, c8.concept_name as rxnorm_name
  from concept c1
  join concept_relationship r1 on r1.concept_id_1=c1.concept_id and r1.invalid_reason is null and r1.relationship_id='Is a'
  join concept c2 on r1.concept_id_2=c2.concept_id
  join concept_relationship r2 on r2.concept_id_1=c2.concept_id and r2.invalid_reason is null and r2.relationship_id='Is a'
  join concept c3 on r2.concept_id_2=c3.concept_id
  join concept_relationship r3 on r3.concept_id_1=c3.concept_id and r3.invalid_reason is null and r3.relationship_id='Is a'
  join concept c4 on r3.concept_id_2=c4.concept_id
  join concept_relationship r4 on r4.concept_id_1=c4.concept_id and r4.invalid_reason is null and r4.relationship_id='Subsumes' -- back to ATC 3
  join concept c5 on r4.concept_id_2=c5.concept_id
  join concept_relationship r5 on r5.concept_id_1=c5.concept_id and r5.invalid_reason is null and r5.relationship_id='Subsumes' -- back to ATC 4
  join concept c6 on r5.concept_id_2=c6.concept_id
  join concept_relationship r6 on r6.concept_id_1=c6.concept_id and r6.invalid_reason is null and r6.relationship_id='Subsumes' -- back to ATC 5
  join concept c7 on r6.concept_id_2=c7.concept_id
  join relationship_to_concept rtc on rtc.concept_code_1 = c7.concept_name -- no need to exclude anything, will join only ingredients
  join concept c8 on c8.concept_id = rtc.concept_id_2
  where c1.concept_class_id='ATC 5th'  and c1.vocabulary_id  = 'ATC' and lower(c1.concept_name) ~ ' and '
  and c2.concept_name ~* ' and |comb| with'
  and c3.concept_name ~* ' and |comb| with'
  and not c4.concept_name ~* ' and |comb| with' -- take narrow ATC2
  --order by c1.concept_code
  ;

  -- 2. ATC for other combinations
  --2.1 ATC 4th
  select
    c1.concept_id as atc5_id, c1.concept_name as atc5_name,
    c2.concept_id as atc4_id, c2.concept_name as atc4_name,
    c4.concept_id as rxnorm_id, c3.concept_name as rxnorm_name
  from concept c1
  join concept_relationship r1 on r1.concept_id_1=c1.concept_id and r1.invalid_reason is null and r1.relationship_id='Is a'
  join concept c2 on r1.concept_id_2=c2.concept_id
  join concept_relationship r2 on r2.concept_id_1=c2.concept_id and r2.invalid_reason is null and r2.relationship_id='Subsumes'
  join concept c3 on r2.concept_id_2=c3.concept_id
  join relationship_to_concept rtc on rtc.concept_code_1 = c3.concept_name -- no need to exclude anything, will join only ingredients
  join concept c4 on c4.concept_id = rtc.concept_id_2
  where c1.concept_class_id='ATC 5th'  and c1.vocabulary_id  = 'ATC'
  and lower(c1.concept_name) ~ 'comb| with '
  and not lower(c2.concept_name) ~ 'comb| with '
  order by c2.concept_code;