Example Queries - dbmi-pitt/np-terminology-imports Wiki

Examples queries and outputs

-- ARCTIUM LAPPA ROOT W/CAMELLIA SINENSIS LEAF/C
select c1.concept_name, c1.concept_id, c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1
inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1
inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_spell_vr_of'
and c1.concept_id = '-7002519'
;
/*
ARCTIUM LAPPA ROOT W/CAMELLIA SINENSIS LEAF/C	-7002519	Green tea	-7001002
ARCTIUM LAPPA ROOT W/CAMELLIA SINENSIS LEAF/C	-7002519	Niu bang zi	-7000973
*/

----
-- ACV PLUS WITH GREEN TEA
select c1.concept_name, c1.concept_id, c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1
inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1
inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_spell_vr_of'
and c1.concept_id = '-7003687'
;
/*
ACV PLUS WITH GREEN TEA	-7003687	Apple cider vinegar	-7001071
ACV PLUS WITH GREEN TEA	-7003687	Green tea	-7001002
*/


-- CAFFEINE
select c1.concept_name, c1.concept_id, c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1
inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1
inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_const_of'
and c1.concept_id = '-7001575'
;
/*
CAFFEINE	-7001575	Cola nitida[Cola nitida]	-7001477
CAFFEINE	-7001575	Paullinia cupana[Paullinia cupana]	-7001368
CAFFEINE	-7001575	Guarana[Paullinia cupana]	-7000653
CAFFEINE	-7001575	Kola[Cola nitida]	-7000266
CAFFEINE	-7001575	Gbanja kola[Cola nitida]	-7000265
CAFFEINE	-7001575	Bitter cola[Cola nitida]	-7000264
*/


-- Test using Kratom 
select * from staging_vocabulary.concept c where concept_class_id = 'Kratom';
/*
concept_id	concept_name	domain_id	vocabulary_id	concept_class_id	standard_concept	concept_code	valid_start_date	valid_end_date	invalid_reason
-7000576	Kratom[Mitragyna speciosa]	NaPDI research	NAPDI	Kratom		d469b67d-e9a6-459f-b209-c59451936336	2000-01-01	2099-02-22	
-7000577	Kratum[Mitragyna speciosa]	NaPDI research	NAPDI	Kratom		d469b67d-e9a6-459f-b209-c59451936336	2000-01-01	2099-02-22	
-7001347	Mitragyna speciosa[Mitragyna speciosa]	NaPDI research	NAPDI	Kratom		d469b67d-e9a6-459f-b209-c59451936336	2000-01-01	2099-02-22		
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_pt'
   and c1.concept_id = '-7000576'
;
/*
Kratom[Mitragyna speciosa]	-7000576	Kratom	-7001107
 */

select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_pt_of'
   and c1.concept_id = '-7001107'
;
/*
Kratom	-7001107	Mitragyna speciosa[Mitragyna speciosa]	-7001347
Kratom	-7001107	Kratum[Mitragyna speciosa]	-7000577
Kratom	-7001107	Kratom[Mitragyna speciosa]	-7000576
*/

select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_has_const'
   and c1.concept_id = '-7000576'
;
/*
Kratom[Mitragyna speciosa]	-7000576	SPECIOGYNINE	-7002253
Kratom[Mitragyna speciosa]	-7000576	SPECIOCILIATIN	-7001997
Kratom[Mitragyna speciosa]	-7000576	PAYNANTHEINE	-7001975
Kratom[Mitragyna speciosa]	-7000576	MITRAGYNINE	-7001817
Kratom[Mitragyna speciosa]	-7000576	7-HYDROXYMITRAGYNINE	-7001801
Kratom[Mitragyna speciosa]	-7000576	MITRAPHYLLINE	-7001560
*/

select distinct c1.concept_name, c1.concept_id,  c3.concept_name, c3.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr1 on c1.concept_id = cr1.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr1.concept_id_2
     inner join staging_vocabulary.concept_relationship cr2 on c2.concept_id = cr2.concept_id_1
     inner join staging_vocabulary.concept c3 on c3.concept_id = cr2.concept_id_2
where cr1.relationship_id = 'napdi_is_pt_of'
   and cr2.relationship_id = 'napdi_has_const'
   and c1.concept_id = '-7001107'
;
/*
Kratom	-7001107	7-HYDROXYMITRAGYNINE	-7001801
Kratom	-7001107	MITRAGYNINE	-7001817
Kratom	-7001107	MITRAPHYLLINE	-7001560
Kratom	-7001107	PAYNANTHEINE	-7001975
Kratom	-7001107	SPECIOCILIATIN	-7001997
Kratom	-7001107	SPECIOGYNINE	-7002253 
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_const_of'
   and c1.concept_id = '-7001817'
;
/*
MITRAGYNINE	-7001817	Mitragyna speciosa[Mitragyna speciosa]	-7001347
MITRAGYNINE	-7001817	Kratum[Mitragyna speciosa]	-7000577
MITRAGYNINE	-7001817	Kratom[Mitragyna speciosa]	-7000576
 */

select distinct c1.concept_name, c1.concept_id,  c3.concept_name, c3.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr1 on c1.concept_id = cr1.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr1.concept_id_2
     inner join staging_vocabulary.concept_relationship cr2 on c2.concept_id = cr2.concept_id_1
     inner join staging_vocabulary.concept c3 on c3.concept_id = cr2.concept_id_2
where cr1.relationship_id = 'napdi_is_const_of'
   and cr2.relationship_id = 'napdi_pt'
   and c1.concept_id = '-7001817'
;
/*
MITRAGYNINE	-7001817	Kratom	-7001107
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_spell_vr'
   and c1.concept_id = '-7001107'
;
/*
Kratom	-7001107	WHOLE HERBS PREMIUM MAENG DA KRATOM	-7003818
Kratom	-7001107	WHITE MAENG DA KRATOM 250G	-7003817
Kratom	-7001107	WHITE MAENG DA HERBAL TEA KRATOM	-7003816
Kratom	-7001107	VIVA ZEN KRATOM	-7003815
Kratom	-7001107	VIVAZEN BOTANICALS MAENG DA KRATOM	-7003814
Kratom	-7001107	UNICORN DUST STRAIN OF KRATOM	-7003813
Kratom	-7001107	TRAIN WRECK KRATOM	-7003812
Kratom	-7001107	TAUNTON BAY SOAP COMPANY RED VEIN TEA-1LB. PACKAGE (KRATOM)	-7003811
Kratom	-7001107	SUPERIOR RED DRAGON KRATOM	-7003810
Kratom	-7001107	SUPER GREEN KRATOM POWDER	-7003809
Kratom	-7001107	SUPER GREEN HORN KRATOM	-7003808
Kratom	-7001107	SLOW-MO HIPPO KRATOM	-7003807
Kratom	-7001107	R.H. NATURAL PRODUCTS KRATOM	-7003806
Kratom	-7001107	RED VEIN MAENG DA (KRATOM)	-7003805
Kratom	-7001107	RED VEIN KRATOM	-7003804
Kratom	-7001107	RED VEIN BORNEO KRATOM	-7003803
Kratom	-7001107	RED THAI KRATOM	-7003802
Kratom	-7001107	RED MAENG DA KRATOM (MITRAGYNA SPECIOSA)	-7003801
Kratom	-7001107	RED DEVIL KRATOM WATER SOLUBLE CBD	-7003800
Kratom	-7001107	RED BORNEO KRATOM BUMBLE BEE	-7003799
Kratom	-7001107	RED BALI KRATOM	-7003798
Kratom	-7001107	RAW FORM ORGANICS MAENG DA 150 RUBY CAPSULES KRATOM	-7003797
Kratom	-7001107	PREMIUM RED MAENG DA KRATOM	-7003796
Kratom	-7001107	PREMIUM RED MAENG DA CRAZY KRATOM	-7003795
Kratom	-7001107	PREMIUM KRATOM PHOENIX RED VEIN BALI	-7003794
Kratom	-7001107	POWDERED KRATOM	-7003793
Kratom	-7001107	O.P.M.S. LIQUID KRATOM	-7003792
Kratom	-7001107	O.P.M.S. KRATOM	-7003791
Kratom	-7001107	NUTRIZONE KRATOM PAIN OUT MAENG DA	-7003790
Kratom	-7001107	NATURE'S REMEDY KRATOM	-7003789
Kratom	-7001107	MITRAGYNINE (KRATOM)	-7003788
Kratom	-7001107	MITRAGYNINE KRATOM	-7003787
Kratom	-7001107	MITRAGYNA SPECIOSA (MITRAGYNINE)	-7003786
Kratom	-7001107	MITRAGYNA SPECIOSA LEAF	-7003785
Kratom	-7001107	MITRAGYNA SPECIOSA KORTHALS (BOTANIC NAME) (KRATOM)	-7003784
Kratom	-7001107	MITRAGYNA SPECIOSA KORTHALS (BOTANIC NAME)	-7003783
Kratom	-7001107	MITRAGYNA SPECIOSA KORTHALS	-7003782
Kratom	-7001107	MITRAGYNA SPECIOSA	-7003781
Kratom	-7001107	MAENG DA POWDER KRATOM HERBAL DIETARY SUPPLEMENT	-7003780
Kratom	-7001107	MAENG DA KRATOM MITROGYNA SPECIOSA	-7003779
Kratom	-7001107	MAENG DA KRATOM	-7003778
Kratom	-7001107	LYFT PREMIUM BALI KRATOM HERBAL SUPPLEMENT POWDER	-7003777
Kratom	-7001107	LUCKY SEVEN KRATOM	-7003776
Kratom	-7001107	KRAVE KRATOM	-7003775
Kratom	-7001107	KRAVE, BLUE MAGIC KRATOM	-7003774
Kratom	-7001107	KRATOM- USED SUPER GREEN, GREEN BALI, RED MAGNEA DA	-7003773
Kratom	-7001107	KRATOM SUPPLEMENT	-7003772
Kratom	-7001107	KRATOM - SPECIFICALLY ^GREEN MALAYSIAN^	-7003771
Kratom	-7001107	KRATOM SILVER THAI	-7003770
Kratom	-7001107	KRATOM RED DRAGON	-7003769
Kratom	-7001107	KRATOM POWDER	-7003768
Kratom	-7001107	KRATOM (MITRAGYNINE)	-7003767
Kratom	-7001107	KRATOM (MITRAGYNA SPECIOSA LEAF)	-7003766
Kratom	-7001107	KRATOM MITRAGYNA SPECIOSA	-7003765
Kratom	-7001107	KRATOM (MITRAGYNA SPECIOSA)	-7003764
Kratom	-7001107	KRATOM (MITRAGYNA) (MITRAGYNINE)	-7003763
Kratom	-7001107	KRATOM (MITRAGYNA)	-7003762
Kratom	-7001107	KRATOM MAGNA RED	-7003761
Kratom	-7001107	(KRATOM) KRAOMA.COM TRANQUIL KRAOMA	-7003760
Kratom	-7001107	KRATOM INDO	-7003759
Kratom	-7001107	KRATOM IN A UNMARKED BAG	-7003758
Kratom	-7001107	KRATOM HERBAL SUPPLEMENT	-7003757
Kratom	-7001107	KRATOM GREEN MAGNA DA	-7003756
Kratom	-7001107	KRATOM EXTRACT	-7003755
Kratom	-7001107	KRATOM ELEPHANT WHITE THAI	-7003754
Kratom	-7001107	KRATOM CAPSULES	-7003753
Kratom	-7001107	KRATOM 3 OZ.	-7003752
Kratom	-7001107	KRATOM	-7003751
Kratom	-7001107	KRAKEN KRATOM	-7003750
Kratom	-7001107	KRABOT KRATOM FINELY GROUND POWDER	-7003749
Kratom	-7001107	KLARITY KRATOM: MAENG DA CAPSULES	-7003748
Kratom	-7001107	INDO KRATOM	-7003747
Kratom	-7001107	HERBAL SUBSTANCE KRATOM	-7003746
Kratom	-7001107	HERBAL SALVATION KRATOM	-7003745
Kratom	-7001107	GREEN STRAIN TROPICAL KRATOM	-7003744
Kratom	-7001107	GREEN M BATIK AND RED BATIK KRATOM	-7003743
Kratom	-7001107	GREEN MALAY KRATOM	-7003742
Kratom	-7001107	GREEN BORNEO KRATOM	-7003741
Kratom	-7001107	FEELIN' GROOVY KRATOM	-7003740
Kratom	-7001107	EMERALD LEAF BALI KRATOM (HERBALSMITRAGYNINE)	-7003739
Kratom	-7001107	EMERALD KRATOM POWDER	-7003738
Kratom	-7001107	EARTH KRATOM ORGANIC RED MAENG DA	-7003737
Kratom	-7001107	CLUB 13 KRATOM MAENG DA RED 90GM	-7003736
Kratom	-7001107	CAROLINA KRATOM RED JONGKONG 100 GRAM POWDER	-7003735
Kratom	-7001107	CALCIUM KRATOMOS	-7003734
Kratom	-7001107	BRILLIANT ELIXIR, CHOCOLATE LOVER W/ KRATOM	-7003733
Kratom	-7001107	BLUE MAGIC KRAVE KRATOM	-7003732
Kratom	-7001107	BALI KRATOM	-7003731
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_spell_vr_of'
    and c1.concept_id = '-7003736'  
;
/*
CLUB 13 KRATOM MAENG DA RED 90GM	-7003736	Kratom	-7001107
*/


---------------------------------------


-- Test using Cinnamon
select * from staging_vocabulary.concept c where concept_class_id = 'Cinnamon';
/*
-7000237	Cassia[Cinnamomum cassia]	NaPDI research	NAPDI	Cinnamon		9e19900a-c256-4161-a4cb-37967de32e4f	2000-01-01	2099-02-22	
-7000238	Cassia cinnamon[Cinnamomum cassia]	NaPDI research	NAPDI	Cinnamon		9e19900a-c256-4161-a4cb-37967de32e4f	2000-01-01	2099-02-22	
-7000239	Chinese cinnamon[Cinnamomum cassia]	NaPDI research	NAPDI	Cinnamon		9e19900a-c256-4161-a4cb-37967de32e4f	2000-01-01	2099-02-22	
-7000240	Chinese cinnamon tree[Cinnamomum cassia]	NaPDI research	NAPDI	Cinnamon		9e19900a-c256-4161-a4cb-37967de32e4f	2000-01-01	2099-02-22	
-7000241	Cinnamon[Cinnamomum cassia]	NaPDI research	NAPDI	Cinnamon		9e19900a-c256-4161-a4cb-37967de32e4f	2000-01-01	2099-02-22	
-7000242	Rou gui[Cinnamomum cassia]	NaPDI research	NAPDI	Cinnamon		9e19900a-c256-4161-a4cb-37967de32e4f	2000-01-01	2099-02-22	
-7000243	Ceylon cinnamon[Cinnamomum verum]	NaPDI research	NAPDI	Cinnamon		0443a27a-fd3c-411d-af4d-617b79790cea	2000-01-01	2099-02-22	
-7000244	Cinnamon[Cinnamomum verum]	NaPDI research	NAPDI	Cinnamon		0443a27a-fd3c-411d-af4d-617b79790cea	2000-01-01	2099-02-22	
-7000245	True cinnamon[Cinnamomum verum]	NaPDI research	NAPDI	Cinnamon		0443a27a-fd3c-411d-af4d-617b79790cea	2000-01-01	2099-02-22	
-7000246	Tvak[Cinnamomum verum]	NaPDI research	NAPDI	Cinnamon		0443a27a-fd3c-411d-af4d-617b79790cea	2000-01-01	2099-02-22	
-7001495	Cinnamomum cassia[Cinnamomum cassia]	NaPDI research	NAPDI	Cinnamon		9e19900a-c256-4161-a4cb-37967de32e4f	2000-01-01	2099-02-22	
-7001523	Cinnamomum verum[Cinnamomum verum]	NaPDI research	NAPDI	Cinnamon		0443a27a-fd3c-411d-af4d-617b79790cea	2000-01-01	2099-02-22	
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_pt'
   and c1.concept_id = '-7000244'
;
/*
Cinnamon[Cinnamomum verum]	-7000244	Cinnamon	-7000970
 */

select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_pt_of'
   and c1.concept_id = '-7000970'
;
/*
Cinnamon	-7000970	Cinnamomum verum[Cinnamomum verum]	-7001523
Cinnamon	-7000970	Cinnamomum cassia[Cinnamomum cassia]	-7001495
Cinnamon	-7000970	Tvak[Cinnamomum verum]	-7000246
Cinnamon	-7000970	True cinnamon[Cinnamomum verum]	-7000245
Cinnamon	-7000970	Cinnamon[Cinnamomum verum]	-7000244
Cinnamon	-7000970	Ceylon cinnamon[Cinnamomum verum]	-7000243
Cinnamon	-7000970	Rou gui[Cinnamomum cassia]	-7000242
Cinnamon	-7000970	Cinnamon[Cinnamomum cassia]	-7000241
Cinnamon	-7000970	Chinese cinnamon tree[Cinnamomum cassia]	-7000240
Cinnamon	-7000970	Chinese cinnamon[Cinnamomum cassia]	-7000239
Cinnamon	-7000970	Cassia cinnamon[Cinnamomum cassia]	-7000238
Cinnamon	-7000970	Cassia[Cinnamomum cassia]	-7000237
*/

select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_has_const'
   and c1.concept_id = '-7001523'
;
/*
Cinnamomum verum[Cinnamomum verum]	-7001523	O-CYMENE	-7002286
Cinnamomum verum[Cinnamomum verum]	-7001523	SAFROLE	-7002273
Cinnamomum verum[Cinnamomum verum]	-7001523	O-METHOXYCINNAMALDEHYDE, (E)-	-7002243
Cinnamomum verum[Cinnamomum verum]	-7001523	LINALOOL, (+/-)-	-7002177
Cinnamomum verum[Cinnamomum verum]	-7001523	BENZALDEHYDE	-7002144
Cinnamomum verum[Cinnamomum verum]	-7001523	CINNAMTANNIN B2	-7002115
Cinnamomum verum[Cinnamomum verum]	-7001523	O-METHOXYCINNAMALDEHYDE	-7002069
Cinnamomum verum[Cinnamomum verum]	-7001523	CINNAMIC ACID	-7002048
Cinnamomum verum[Cinnamomum verum]	-7001523	METHYL EUGENOL	-7002019
Cinnamomum verum[Cinnamomum verum]	-7001523	BENZYL BENZOATE	-7002013
Cinnamomum verum[Cinnamomum verum]	-7001523	PHELLANDRENE	-7001967
Cinnamomum verum[Cinnamomum verum]	-7001523	PINENE	-7001897
Cinnamomum verum[Cinnamomum verum]	-7001523	CINNAMTANNIN B1	-7001881
Cinnamomum verum[Cinnamomum verum]	-7001523	P-CYMENE	-7001847
Cinnamomum verum[Cinnamomum verum]	-7001523	CINNAMYL ACETATE	-7001806
Cinnamomum verum[Cinnamomum verum]	-7001523	CINNAMYL ALCOHOL	-7001744
Cinnamomum verum[Cinnamomum verum]	-7001523	EUCALYPTOL	-7001688
Cinnamomum verum[Cinnamomum verum]	-7001523	M-CYMENE	-7001654
Cinnamomum verum[Cinnamomum verum]	-7001523	EUGENOL	-7001636
Cinnamomum verum[Cinnamomum verum]	-7001523	CARYOPHYLLENE	-7001627
Cinnamomum verum[Cinnamomum verum]	-7001523	CINNAMALDEHYDE	-7001589
Cinnamomum verum[Cinnamomum verum]	-7001523	COUMARIN	-7001583
*/

select distinct c1.concept_name, c1.concept_id,  c3.concept_name, c3.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr1 on c1.concept_id = cr1.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr1.concept_id_2
     inner join staging_vocabulary.concept_relationship cr2 on c2.concept_id = cr2.concept_id_1
     inner join staging_vocabulary.concept c3 on c3.concept_id = cr2.concept_id_2
where cr1.relationship_id = 'napdi_is_pt_of'
   and cr2.relationship_id = 'napdi_has_const'
   and c1.concept_id = '-7000970'
;
/*
Cinnamon	-7000970	2-METHOXYCINNAMIC ACID	-7001564
Cinnamon	-7000970	BENZALDEHYDE	-7002144
Cinnamon	-7000970	BENZYL BENZOATE	-7002013
Cinnamon	-7000970	CARYOPHYLLENE	-7001627
Cinnamon	-7000970	CHINESE CINNAMON OIL	-7002222
Cinnamon	-7000970	CINNAMALDEHYDE	-7001589
Cinnamon	-7000970	CINNAMIC ACID	-7002048
Cinnamon	-7000970	CINNAMTANNIN B1	-7001881
Cinnamon	-7000970	CINNAMTANNIN B2	-7002115
Cinnamon	-7000970	CINNAMYL ACETATE	-7001806
Cinnamon	-7000970	CINNAMYL ALCOHOL	-7001744
Cinnamon	-7000970	COUMARIN	-7001583
Cinnamon	-7000970	EUCALYPTOL	-7001688
Cinnamon	-7000970	EUGENOL	-7001636
Cinnamon	-7000970	LINALOOL, (+/-)-	-7002177
Cinnamon	-7000970	M-CYMENE	-7001654
Cinnamon	-7000970	METHYL EUGENOL	-7002019
Cinnamon	-7000970	O-CYMENE	-7002286
Cinnamon	-7000970	O-METHOXYCINNAMALDEHYDE	-7002069
Cinnamon	-7000970	O-METHOXYCINNAMALDEHYDE, (E)-	-7002243
Cinnamon	-7000970	P-CYMENE	-7001847
Cinnamon	-7000970	PHELLANDRENE	-7001967
Cinnamon	-7000970	PINENE	-7001897
Cinnamon	-7000970	SAFROLE	-7002273
Cinnamon	-7000970	SALICYLALDEHYDE	-7001910 
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_const_of'
   and c1.concept_id = '-7001583'
;
/*
COUMARIN	-7001583	Cinnamomum verum[Cinnamomum verum]	-7001523
COUMARIN	-7001583	Cinnamomum cassia[Cinnamomum cassia]	-7001495
COUMARIN	-7001583	Peumus boldus[Peumus boldus]	-7001451
COUMARIN	-7001583	Boldu[Peumus boldus]	-7000658
COUMARIN	-7001583	Boldo tree[Peumus boldus]	-7000657
COUMARIN	-7001583	Boldo[Peumus boldus]	-7000656
COUMARIN	-7001583	Tvak[Cinnamomum verum]	-7000246
COUMARIN	-7001583	True cinnamon[Cinnamomum verum]	-7000245
COUMARIN	-7001583	Cinnamon[Cinnamomum verum]	-7000244
COUMARIN	-7001583	Ceylon cinnamon[Cinnamomum verum]	-7000243
COUMARIN	-7001583	Rou gui[Cinnamomum cassia]	-7000242
COUMARIN	-7001583	Cinnamon[Cinnamomum cassia]	-7000241
COUMARIN	-7001583	Chinese cinnamon tree[Cinnamomum cassia]	-7000240
COUMARIN	-7001583	Chinese cinnamon[Cinnamomum cassia]	-7000239
COUMARIN	-7001583	Cassia cinnamon[Cinnamomum cassia]	-7000238
COUMARIN	-7001583	Cassia[Cinnamomum cassia]	-7000237
*/

select distinct c1.concept_name, c1.concept_id,  c3.concept_name, c3.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr1 on c1.concept_id = cr1.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr1.concept_id_2
     inner join staging_vocabulary.concept_relationship cr2 on c2.concept_id = cr2.concept_id_1
     inner join staging_vocabulary.concept c3 on c3.concept_id = cr2.concept_id_2
where cr1.relationship_id = 'napdi_is_const_of'
   and cr2.relationship_id = 'napdi_pt'
   and c1.concept_id = '-7001583'
;
/*
COUMARIN	-7001583	Boldu	-7001246
COUMARIN	-7001583	Cinnamon	-7000970
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_spell_vr'
   and c1.concept_id = '-7000970'
;
/*
Cinnamon	-7000970	SAIREITO /08000901/ (ALISMA PLANTAGO-AQUATICA VAR. ORIENTALE TUBER, ATRACTYLODES LANCEA RHIZOME, BUPLEURUM FALCATUM ROOT, CINNAMOMUM CASSIA BARK, GLYCYRRHIZA SPP. ROOT, PANAX GINSENG ROOT, PINELLIA TERNATA TUBER, POLYPORUS UMBELLATUS SCLEROTIUM, PORIA COC	-7004944
Cinnamon	-7000970	CHINESE LICORICE (+) CHINESE PEONY (+) CINNAMON (+) EPHEDRA (+) GINGER	-7004882
Cinnamon	-7000970	LOOSE LEAF TEA (YERBA MATE) W/DANDELION ROOT+TUMERIC+CINNAMON+OREGAN+THYME	-7004332
Cinnamon	-7000970	SAIREITO /08000901/ (ALISMA PLANTAGO-AQUATICA VAR. ORIENTALE TUBER, ATRACTYLODES LANCEA RHIZOME, BUPLEURUM FALCATUM ROOT, CINNAMOMUM CASSIA BARK, GLYCYRRHIZA SPP. ROOT, PANAX GINSENG ROOT, PINELLIA TERNATA TUBER, POLYPORUS UMBELLATUS SCLEROTIUM, PORIA COC	-7004001
Cinnamon	-7000970	WALGREENS BRAND CINNAMON CAPSULES	-7003034
Cinnamon	-7000970	VITAMIN E CINNAMON	-7003033
Cinnamon	-7000970	VITAMIN C-CINNAMON	-7003032
Cinnamon	-7000970	S.M. (CALCIUM CARBONATE, CINNAMOMUM VERUM POWDER, COPTIS TRIFOLIA, DIA	-7003031
Cinnamon	-7000970	SHOSEIRYUTO [ASARUM SPP. ROOT;CINNAMOMUM CASSIA BARK;EPHEDRA SPP. HERB	-7003030
Cinnamon	-7000970	SHOSEIRYUTO [ASARUM SPP. ROOT;CINNAMOMUM CASSIA BARK;EPHEDRA SPP.	-7003029
Cinnamon	-7000970	SAIREITO /08000901/ (ALISMA PLANTAGO-AQUATICA VAR. ORIENTALE TUBER, ATRACTYLODES LANCEA RHIZOME, BUPLEURUM FALCATUM ROOT, CINNAMOMUM CASSIA BARK, GLYCYRRHIZA SPP. ROOT, PANAX GINSENG ROOT, PINELLIA TERNATA TUBER, POLYPORUS UMBELLATUS SCLEROTIUM, PORIA COC	-7003028
Cinnamon	-7000970	RYOKEIJUTSUKANTO [ATRACTYLODES LANCEA RHIZOME;CINNAMOMUM CASSIA BARK;G	-7003027
Cinnamon	-7000970	PURITAN'S PRIDE CINNAMON WITH HIGH POTENCY CHROMIUM COMPLEX	-7003026
Cinnamon	-7000970	MISTURA CARMINATIVA (CARAWAY (+) CARDAMOM SEED (+) CINNAMON (+) GLYCER	-7003025
Cinnamon	-7000970	MENTHOL/THYMOL/SYZYGIUM AROMATICUM OIL/CINNAMOMUM VERUM BARK OIL/EUCALYPTUS GLOBULUS OIL/FOENICULUM	-7003024
Cinnamon	-7000970	MAOTO (CINNAMOMUM CASSIA BARK, EPHEDRA SPP. HERB, GLYCYRRHIZA SPP. ROOT, PRUNIUS SPP. SEED)	-7003023
Cinnamon	-7000970	LOOSE LEAF TEA (YERBA MATE) W/DANDELION ROOT+TUMERIC+CINNAMON+OREGAN+THYME	-7003022
Cinnamon	-7000970	KEISHIKASHAKUYAKUTO [CINNAMOMUM CASSIA BARK;GLYCYRRHIZA SPP. ROOT;PAEO	-7003021
Cinnamon	-7000970	KAKKONTOKASENKYUSHIN'I [CINNAMOMUM CASSIA BARK;CNIDIUM OFFICINALE RHIZ	-7003020
Cinnamon	-7000970	KAKKONTO [CINNAMOMUM CASSIA BARK;EPHEDRA SPP. HERB;GLYCYRRHIZA SPP. RO	-7003019
Cinnamon	-7000970	KAKKONTO [CINNAMOMUM CASSIA BARK;EPHEDRA SPP.	-7003018
Cinnamon	-7000970	KAIGEN (CAFFEINE, CINNAMON, GLYCYRRHIZA EXTRACT, METHYLEPHEDRINE HYDRO	-7003017
Cinnamon	-7000970	INNAMON (CINNAMON VERUM) CAPSULE	-7003016
Cinnamon	-7000970	HONEY + CINNAMON	-7003015
Cinnamon	-7000970	HERBAL SUPPLEMENT WITH CINNAMON	-7003014
Cinnamon	-7000970	HACHIMIJIOGAN (ACONITUM SPP. PROCESSED ROOT, ALISMA PLANTAGO-AQUATICA VAR. ORIENTALE TUBER, CINNAMOMUM CASSIA BARK, CORNUS OFFICINALIS FRUIT, DIOSCOREA SPP. RHIZOME, PAEONIA X SUFFRUTICOSA ROOT BARK, PORIA COCOS SCLEROTIUM, REHMANNIA GLUTINOSA ROOT)	-7003013
Cinnamon	-7000970	GOSHAJINKIGAN (ACHYRANTHES BIDENTATA ROOT, ACONITUM SPP. PROCESSED ROOT, ALISMA PLANTAGO-AQUATICA VAR. ORIENTALE TUBER, CINNAMOMUM CASSIA BARK, CORNUS OFFICINALIS FRUIT, DIOSCOREA SPP. RHIZOME, PAEONIA X SUFFRUTICOSA ROOT BARK, PLANTAGO ASIATICA SEED, POR	-7003012
Cinnamon	-7000970	GOREISAN /08015901/ (ALISMA PLANTAGO-AQUATICA VAR. ORIENTALE TUBER, ATRACTYLODES SPP. RHIZOME, CINNAMOMIUM CASSIA BARK, POLYPORUS UMBELLATUS SCLEROTIUM, PORIA COCOS SCLEROTIUM)	-7003011
Cinnamon	-7000970	^GOOD HERBS ORIGINAL^ TEA - ROOBOIS, ROSEHIPS, CHAMOMILE, CINNAMON, LE	-7003010
Cinnamon	-7000970	COQ10 WITH CINNAMON	-7003009
Cinnamon	-7000970	CO-Q 10 W/CINNAMON	-7003008
Cinnamon	-7000970	CONNAMON	-7003007
Cinnamon	-7000970	CNNAMON (CINNAMOMUM VERUM) (CINNAMOMUM VERUM)	-7003006
Cinnamon	-7000970	CNNAMON	-7003005
Cinnamon	-7000970	CINSULIN (CINNAMON AND CHROMIUM PICOLINATE)	-7003004
Cinnamon	-7000970	CINSULILN (CINNAMON, VITAMIN D3, 500IU)	-7003003
Cinnamon	-7000970	CINNULIN PF (WATER CINNAMON EXTRACT)	-7003002
Cinnamon	-7000970	CINNOMON	-7003001
Cinnamon	-7000970	CINNNAMON	-7003000
Cinnamon	-7000970	CINNMON	-7002999
Cinnamon	-7000970	CINNIMOV	-7002998
Cinnamon	-7000970	CINNIMON	-7002997
Cinnamon	-7000970	CINNIMAN	-7002996
Cinnamon	-7000970	CINNEMON	-7002995
Cinnamon	-7000970	CINNEMIN	-7002994
Cinnamon	-7000970	CINNAOMIN	-7002993
Cinnamon	-7000970	CINNAMON WITH HONEY AND TEA COMBINATION	-7002992
Cinnamon	-7000970	CINNAMON WITH CHROMIUM (CINNAMOMUM VERUM)	-7002991
Cinnamon	-7000970	CINNAMON WITH CHROMIUM	-7002990
Cinnamon	-7000970	CINNAMON W/ CHROMIUM	-7002989
Cinnamon	-7000970	CINNAMON W/CHROMIUM	-7002988
Cinnamon	-7000970	CINNAMON VERUM	-7002987
Cinnamon	-7000970	CINNAMON TEA	-7002986
Cinnamon	-7000970	CINNAMON RED HOTS	-7002985
Cinnamon	-7000970	CINNAMON PLUS CHROMIUM METABOLISM SUPPORT	-7002984
Cinnamon	-7000970	CINNAMON PLUS CHROMIUM	-7002983
Cinnamon	-7000970	CINNAMON PILLS (CINNAMOMUM VERUM)	-7002982
Cinnamon	-7000970	CINNAMON GARLIC	-7002981
Cinnamon	-7000970	CINNAMON EXTRACT TABLETS	-7002980
Cinnamon	-7000970	CINNAMON EXTRACT [CINNAMOMUM CASSIA TWIG]	-7002979
Cinnamon	-7000970	CINNAMON EXTRACT [CINNAMOMUM BURMANNI]	-7002978
Cinnamon	-7000970	CINNAMON EXTRACT	-7002977
Cinnamon	-7000970	CINNAMON CR	-7002976
Cinnamon	-7000970	CINNAMON COMPLEX	-7002975
Cinnamon	-7000970	CINNAMON (CNINAMOMUM VERUM)	-7002974
Cinnamon	-7000970	CINNAMON CITRACAL + D CLOTRIMAZOLE	-7002973
Cinnamon	-7000970	CINNAMON (CINNAMOUM VERUM) (CAPSULES)	-7002972
Cinnamon	-7000970	CINNAMON (CINNAMON VERUM)	-7002971
Cinnamon	-7000970	CINNAMON (CINNAMOM VERUM)	-7002970
Cinnamon	-7000970	CINNAMON (CINNAMOMUN VERUM) (CINNAMOMUM VERUM)	-7002969
Cinnamon	-7000970	CINNAMON (CINNAMOMUN VERUM)	-7002968
Cinnamon	-7000970	CINNAMON (CINNAMOMUM VERUM)(UNKNOWN)	-7002967
Cinnamon	-7000970	CINNAMON (CINNAMOMUM VERUM) (UNKNOWN)	-7002966
Cinnamon	-7000970	CINNAMON (CINNAMOMUM VERUM) TABLET, 500 MG	-7002965
Cinnamon	-7000970	CINNAMON(CINNAMOMUM VERUM)	-7002964
Cinnamon	-7000970	CINNAMON (CINNAMOMUM VERUM)	-7002963
Cinnamon	-7000970	CINNAMON ( CINNAMOMUM VERUM)	-7002962
Cinnamon	-7000970	CINNAMON [CINNAMOMUM CASSIA BARK]	-7002961
Cinnamon	-7000970	CINNAMON (CINAMOMUM VERUM)	-7002960
Cinnamon	-7000970	CINNAMON/CHRONDROITIN	-7002959
Cinnamon	-7000970	CINNAMON/CHROMIUM	-7002958
Cinnamon	-7000970	CINNAMON + CHROMIUM	-7002957
Cinnamon	-7000970	CINNAMON CHROMIUM	-7002956
Cinnamon	-7000970	CINNAMON/CHROME	-7002955
Cinnamon	-7000970	CINNAMON+CHROM	-7002954
Cinnamon	-7000970	CINNAMON/CHRM	-7002953
Cinnamon	-7000970	CINNAMON CAPSULE (CINNAMOMUM VERUM) (CINNAMOMUM VERUM)	-7002952
Cinnamon	-7000970	CINNAMON BARK W/CHROMIMUN	-7002951
Cinnamon	-7000970	CINNAMON BARK EXTRACT	-7002950
Cinnamon	-7000970	CINNAMON BARK CASSIA	-7002949
Cinnamon	-7000970	CINNAMON AND HONEY	-7002948
Cinnamon	-7000970	CINNAMON AND GREEN TEA	-7002947
Cinnamon	-7000970	CINNAMON AND CHROMIUM	-7002946
Cinnamon	-7000970	CINNAMON 500 MG PLUS CHROMIUM METABOLISM SUPPORT	-7002945
Cinnamon	-7000970	CINNAMON 1000/PLUS CHROMIUM	-7002944
Cinnamon	-7000970	CINNAMON 1000 MG WITH CHROMIUM	-7002943
Cinnamon	-7000970	CINNAMON                           /05656704/	-7002942
Cinnamon	-7000970	CINNAMON                           /05656701/	-7002941
Cinnamon	-7000970	CINNAMON                           /01647506/	-7002940
Cinnamon	-7000970	CINNAMON /01647501/ (CINNAMOMUM VERUM)	-7002939
Cinnamon	-7000970	CINNAMON /01647501/	-7002938
Cinnamon	-7000970	CINNAMON                           /01647501/	-7002937
Cinnamon	-7000970	CINNAMOMUM VERUM OIL	-7002936
Cinnamon	-7000970	CINNAMOMUM VERUM (CINNAMOMUM VERUM)	-7002935
Cinnamon	-7000970	CINNAMOMUM VERUM BARK	-7002934
Cinnamon	-7000970	CINNAMOMUM VERUM	-7002933
Cinnamon	-7000970	CINNAMOMUM SPP.	-7002932
Cinnamon	-7000970	CINNAMOMUM CASSIA BARK, PAEONIA LACTIFLORA ROOT, PAEONIA X SUFFRUTICOS	-7002931
Cinnamon	-7000970	CINNAMOMUM CASSIA BARK/EPHEDRA SPP. HERB/GLYCYRRHIZA SPP. ROOT/PAEONIA LACTIFLORA ROOT/PUERARIA LOBA	-7002930
Cinnamon	-7000970	CINNAMOMUM CASSIA BARK	-7002929
Cinnamon	-7000970	CINNAMOMUM CASSIA	-7002928
Cinnamon	-7000970	CINNAMOMUM CAMPHORA	-7002927
Cinnamon	-7000970	CINNAMOM	-7002926
Cinnamon	-7000970	CINNAMO	-7002925
Cinnamon	-7000970	CINNAMMON	-7002924
Cinnamon	-7000970	CINNAMINT	-7002923
Cinnamon	-7000970	CINNAMIN	-7002922
Cinnamon	-7000970	CINMMON	-7002921
Cinnamon	-7000970	CINAMON	-7002920
Cinnamon	-7000970	CINAMMON	-7002919
Cinnamon	-7000970	CINAMMOMUM VERUM	-7002918
Cinnamon	-7000970	CINAMMN	-7002917
Cinnamon	-7000970	CINAMIN	-7002916
Cinnamon	-7000970	CINAIMON	-7002915
Cinnamon	-7000970	CIMNAMON	-7002914
Cinnamon	-7000970	CIMMAMON	-7002913
Cinnamon	-7000970	CHROMIUM W/CINNAMOMUM VERUM/ZINC	-7002912
Cinnamon	-7000970	CHROMIUM/CINNAMON	-7002911
Cinnamon	-7000970	CHROMIUM;CINNAMON	-7002910
Cinnamon	-7000970	CHROMIUM/CINNAMOMUM VERUM/ZINC	-7002909
Cinnamon	-7000970	CHROMIUM, CINNAMOMUM VERUM	-7002908
Cinnamon	-7000970	CHINESE LICORICE (+) CHINESE PEONY (+) CINNAMON (+) EPHEDRA (+) GINGER	-7002907
Cinnamon	-7000970	CASSIA (CINNAMON)	-7002906
Cinnamon	-7000970	CASSIA CINNAMON	-7002905
Cinnamon	-7000970	CASSIA (CINNAMOMUM VERUM) (CINNAMOMUM VERUM)	-7002904
Cinnamon	-7000970	CASSIA	-7002903
Cinnamon	-7000970	CABAGIN (CINNAMON, POWDERED, DIASTASE, LIQURICE, MENTHOL, NISIN, SCOPO	-7002902
Cinnamon	-7000970	BERBERTINE + CINNAMON BARK	-7002901
Cinnamon	-7000970	ATRACTYLODES LANCEA RHIZOME/ALISMA RHIZOME/POLYPORUS SCLEROTIUM/PORIA SCLEROTIUM/CINNAMON BARK	-7002900
Cinnamon	-7000970	ASARUM SPP. ROOT, CINNAMOMUM CASSIA BARK, EPHEDRA SPP. HERB, GLYCYRRHI	-7002899
Cinnamon	-7000970	ALPHA LIPSIS SOID.CINNAMON	-7002898
Cinnamon	-7000970	CINNAMON AND GREEN TEA	-7002611
Cinnamon	-7000970	CINNAMON GARLIC	-7002367
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_spell_vr_of'
    and c1.concept_id = '-7002903'  
;
/*
CASSIA	-7002903	Cinnamon	-7000970
*/


---------------------------------------


-- Test using Hemp extract
select * from staging_vocabulary.concept c where concept_class_id = 'Hemp extract';
/*
-7000188	Cannibidiol[Cannabis sativa]	NaPDI research	NAPDI	Hemp extract		f172ae99-9515-4bff-a8ed-4e68afad1b1d	2000-01-01	2099-02-22	
-7000189	Da ma[Cannabis sativa]	NaPDI research	NAPDI	Hemp extract		f172ae99-9515-4bff-a8ed-4e68afad1b1d	2000-01-01	2099-02-22	
-7000190	Hemp[Cannabis sativa]	NaPDI research	NAPDI	Hemp extract		f172ae99-9515-4bff-a8ed-4e68afad1b1d	2000-01-01	2099-02-22	
-7000191	Hemp extract[Cannabis sativa]	NaPDI research	NAPDI	Hemp extract		f172ae99-9515-4bff-a8ed-4e68afad1b1d	2000-01-01	2099-02-22	
-7000192	Marijuana[Cannabis sativa]	NaPDI research	NAPDI	Hemp extract		f172ae99-9515-4bff-a8ed-4e68afad1b1d	2000-01-01	2099-02-22	
-7001415	Cannabis sativa[Cannabis sativa]	NaPDI research	NAPDI	Hemp extract		f172ae99-9515-4bff-a8ed-4e68afad1b1d	2000-01-01	2099-02-22	
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_pt'
   and c1.concept_id = '-7000192'
;
/*
Marijuana[Cannabis sativa]	-7000192	Hemp extract	-7000988
*/

select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_pt_of'
   and c1.concept_id = '-7000988'
;
/*
Hemp extract	-7000988	Cannabis sativa[Cannabis sativa]	-7001415
Hemp extract	-7000988	Marijuana[Cannabis sativa]	-7000192
Hemp extract	-7000988	Hemp extract[Cannabis sativa]	-7000191
Hemp extract	-7000988	Hemp[Cannabis sativa]	-7000190
Hemp extract	-7000988	Da ma[Cannabis sativa]	-7000189
Hemp extract	-7000988	Cannibidiol[Cannabis sativa]	-7000188
*/

select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_has_const'
   and c1.concept_id = '-7000192'
;
/*
Marijuana[Cannabis sativa]	-7000192	4-THUJANOL, CIS-(+/-)-	-7002284
Marijuana[Cannabis sativa]	-7000192	.GAMMA.-BISABOLENE, (Z)-	-7002246
Marijuana[Cannabis sativa]	-7000192	.ALPHA.-CADINENE, (+)-	-7002233
Marijuana[Cannabis sativa]	-7000192	CAMPHENE	-7002231
Marijuana[Cannabis sativa]	-7000192	.ALPHA.-LONGIPINENE	-7002199
Marijuana[Cannabis sativa]	-7000192	.BETA.-PHELLANDRENE	-7002186
Marijuana[Cannabis sativa]	-7000192	QUERCETIN	-7002182
Marijuana[Cannabis sativa]	-7000192	CANNABIDIVARIN	-7002178
Marijuana[Cannabis sativa]	-7000192	LINALOOL, (+/-)-	-7002177
Marijuana[Cannabis sativa]	-7000192	.ALPHA.-SELINENE	-7002158
Marijuana[Cannabis sativa]	-7000192	CANNABICITRAN	-7002146
Marijuana[Cannabis sativa]	-7000192	.BETA.-EUDESMOL	-7002136
Marijuana[Cannabis sativa]	-7000192	.ALPHA.-PINENE	-7002133
Marijuana[Cannabis sativa]	-7000192	.BETA.-OCIMENE, (3E)-	-7002094
Marijuana[Cannabis sativa]	-7000192	CANNABIGEROLIC ACID	-7002086
Marijuana[Cannabis sativa]	-7000192	.BETA.-FARNESENE, (6Z)-	-7002085
Marijuana[Cannabis sativa]	-7000192	CANNABICHROMENE	-7002077
Marijuana[Cannabis sativa]	-7000192	TETRAHYDROCANNABIVARIN	-7002076
Marijuana[Cannabis sativa]	-7000192	.ALPHA.-TERPINEOL	-7002068
Marijuana[Cannabis sativa]	-7000192	FENCHONE, (+/-)-	-7002061
Marijuana[Cannabis sativa]	-7000192	CANNABIVARIN	-7002038
Marijuana[Cannabis sativa]	-7000192	.DELTA.-9-TETRAHYDROCANNABIORCOLIC ACID B	-7002024
Marijuana[Cannabis sativa]	-7000192	CANNABIDIOL	-7001998
Marijuana[Cannabis sativa]	-7000192	YLANGENE	-7001992
Marijuana[Cannabis sativa]	-7000192	CANNABICHROMENIC ACID, (+)-	-7001986
Marijuana[Cannabis sativa]	-7000192	HUMULENE	-7001984
Marijuana[Cannabis sativa]	-7000192	TERPINOLENE	-7001953
Marijuana[Cannabis sativa]	-7000192	LINOLENIC ACID	-7001950
Marijuana[Cannabis sativa]	-7000192	.BETA.-CARYOPHYLLENE OXIDE	-7001939
Marijuana[Cannabis sativa]	-7000192	.DELTA.-9-TETRAHYDROCANNABIORCOLIC ACID A	-7001933
Marijuana[Cannabis sativa]	-7000192	CANNABINOL	-7001923
Marijuana[Cannabis sativa]	-7000192	3-BUTYL-.DELTA.9-TETRAHYDROCANNABINOL	-7001891
Marijuana[Cannabis sativa]	-7000192	.ALPHA.-BISABOLOL, (-)-EPI-	-7001890
Marijuana[Cannabis sativa]	-7000192	.DELTA.-7-CIS-ISOTETRAHYDROCANNABIVARIN	-7001888
Marijuana[Cannabis sativa]	-7000192	N-CAFFEOYLTYRAMINE	-7001876
Marijuana[Cannabis sativa]	-7000192	.BETA.-PINENE	-7001869
Marijuana[Cannabis sativa]	-7000192	BORNEOL	-7001852
Marijuana[Cannabis sativa]	-7000192	CANNABICHROMEVARIN	-7001843
Marijuana[Cannabis sativa]	-7000192	.BETA.-OCIMENE, (3Z)-	-7001835
Marijuana[Cannabis sativa]	-7000192	IPSDIENOL	-7001827
Marijuana[Cannabis sativa]	-7000192	APIGENIN	-7001819
Marijuana[Cannabis sativa]	-7000192	ORIENTIN	-7001815
Marijuana[Cannabis sativa]	-7000192	.ALPHA.-FARNESENE	-7001809
Marijuana[Cannabis sativa]	-7000192	.BETA.-ELEMENE	-7001771
Marijuana[Cannabis sativa]	-7000192	.DELTA.9-TETRAHYDROCANNABINOLIC ACID	-7001767
Marijuana[Cannabis sativa]	-7000192	OLEIC ACID	-7001759
Marijuana[Cannabis sativa]	-7000192	CANNABIGEROLIC ACID MONOMETHYL ETHER	-7001747
Marijuana[Cannabis sativa]	-7000192	.ALPHA.-BERGAMOTENE, (E)-(-)-	-7001746
Marijuana[Cannabis sativa]	-7000192	LUTEOLIN	-7001740
Marijuana[Cannabis sativa]	-7000192	.GAMMA.-BISABOLENE, (E)-	-7001739
Marijuana[Cannabis sativa]	-7000192	.DELTA.8-TETRAHYDROCANNABINOL	-7001735
Marijuana[Cannabis sativa]	-7000192	3-CARENE	-7001732
Marijuana[Cannabis sativa]	-7000192	VITEXIN	-7001720
Marijuana[Cannabis sativa]	-7000192	MYRCENE	-7001715
Marijuana[Cannabis sativa]	-7000192	.DELTA.1-TETRAHYDROCANNABIORCOL	-7001701
Marijuana[Cannabis sativa]	-7000192	DRONABINOL	-7001684
Marijuana[Cannabis sativa]	-7000192	GROSSAMIDE	-7001678
Marijuana[Cannabis sativa]	-7000192	.ALPHA.-EUDESMOL	-7001661
Marijuana[Cannabis sativa]	-7000192	KAEMPFEROL	-7001656
Marijuana[Cannabis sativa]	-7000192	CANNABIDIOLIC ACID	-7001650
Marijuana[Cannabis sativa]	-7000192	.BETA.-FENCHYL ALCOHOL	-7001638
Marijuana[Cannabis sativa]	-7000192	.ALPHA.-THUJENE, (+/-)-	-7001633
Marijuana[Cannabis sativa]	-7000192	CARYOPHYLLENE	-7001627
Marijuana[Cannabis sativa]	-7000192	LIMONENE, (+/-)-	-7001618
Marijuana[Cannabis sativa]	-7000192	CANNABIGEROVARIN	-7001615
Marijuana[Cannabis sativa]	-7000192	LINOLEIC ACID	-7001582
Marijuana[Cannabis sativa]	-7000192	GUAIOL	-7001579
Marijuana[Cannabis sativa]	-7000192	.DELTA.-9-CIS-TETRAHYDROCANNABINOL, (-)-	-7001557
*/

select distinct c1.concept_name, c1.concept_id,  c3.concept_name, c3.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr1 on c1.concept_id = cr1.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr1.concept_id_2
     inner join staging_vocabulary.concept_relationship cr2 on c2.concept_id = cr2.concept_id_1
     inner join staging_vocabulary.concept c3 on c3.concept_id = cr2.concept_id_2
where cr1.relationship_id = 'napdi_is_pt_of'
   and cr2.relationship_id = 'napdi_has_const'
   and c1.concept_id = '-7000988'
;
/*
Hemp extract	-7000988	3-BUTYL-.DELTA.9-TETRAHYDROCANNABINOL	-7001891
Hemp extract	-7000988	3-CARENE	-7001732
Hemp extract	-7000988	4-THUJANOL, CIS-(+/-)-	-7002284
Hemp extract	-7000988	.ALPHA.-BERGAMOTENE, (E)-(-)-	-7001746
Hemp extract	-7000988	.ALPHA.-BISABOLOL, (-)-EPI-	-7001890
Hemp extract	-7000988	.ALPHA.-CADINENE, (+)-	-7002233
Hemp extract	-7000988	.ALPHA.-EUDESMOL	-7001661
Hemp extract	-7000988	.ALPHA.-FARNESENE	-7001809
Hemp extract	-7000988	.ALPHA.-LONGIPINENE	-7002199
Hemp extract	-7000988	.ALPHA.-PINENE	-7002133
Hemp extract	-7000988	.ALPHA.-SELINENE	-7002158
Hemp extract	-7000988	.ALPHA.-TERPINEOL	-7002068
Hemp extract	-7000988	.ALPHA.-THUJENE, (+/-)-	-7001633
Hemp extract	-7000988	APIGENIN	-7001819
Hemp extract	-7000988	.BETA.-CARYOPHYLLENE OXIDE	-7001939
Hemp extract	-7000988	.BETA.-ELEMENE	-7001771
Hemp extract	-7000988	.BETA.-EUDESMOL	-7002136
Hemp extract	-7000988	.BETA.-FARNESENE, (6Z)-	-7002085
Hemp extract	-7000988	.BETA.-FENCHYL ALCOHOL	-7001638
Hemp extract	-7000988	.BETA.-OCIMENE, (3E)-	-7002094
Hemp extract	-7000988	.BETA.-OCIMENE, (3Z)-	-7001835
Hemp extract	-7000988	.BETA.-PHELLANDRENE	-7002186
Hemp extract	-7000988	.BETA.-PINENE	-7001869
Hemp extract	-7000988	BORNEOL	-7001852
Hemp extract	-7000988	CAMPHENE	-7002231
Hemp extract	-7000988	CANNABICHROMENE	-7002077
Hemp extract	-7000988	CANNABICHROMENIC ACID, (+)-	-7001986
Hemp extract	-7000988	CANNABICHROMEVARIN	-7001843
Hemp extract	-7000988	CANNABICITRAN	-7002146
Hemp extract	-7000988	CANNABIDIOL	-7001998
Hemp extract	-7000988	CANNABIDIOLIC ACID	-7001650
Hemp extract	-7000988	CANNABIDIVARIN	-7002178
Hemp extract	-7000988	CANNABIGEROLIC ACID	-7002086
Hemp extract	-7000988	CANNABIGEROLIC ACID MONOMETHYL ETHER	-7001747
Hemp extract	-7000988	CANNABIGEROVARIN	-7001615
Hemp extract	-7000988	CANNABINOL	-7001923
Hemp extract	-7000988	CANNABIVARIN	-7002038
Hemp extract	-7000988	CARYOPHYLLENE	-7001627
Hemp extract	-7000988	.DELTA.1-TETRAHYDROCANNABIORCOL	-7001701
Hemp extract	-7000988	.DELTA.-7-CIS-ISOTETRAHYDROCANNABIVARIN	-7001888
Hemp extract	-7000988	.DELTA.8-TETRAHYDROCANNABINOL	-7001735
Hemp extract	-7000988	.DELTA.-9-CIS-TETRAHYDROCANNABINOL, (-)-	-7001557
Hemp extract	-7000988	.DELTA.9-TETRAHYDROCANNABINOLIC ACID	-7001767
Hemp extract	-7000988	.DELTA.-9-TETRAHYDROCANNABIORCOLIC ACID A	-7001933
Hemp extract	-7000988	.DELTA.-9-TETRAHYDROCANNABIORCOLIC ACID B	-7002024
Hemp extract	-7000988	DRONABINOL	-7001684
Hemp extract	-7000988	FENCHONE, (+/-)-	-7002061
Hemp extract	-7000988	.GAMMA.-BISABOLENE, (E)-	-7001739
Hemp extract	-7000988	.GAMMA.-BISABOLENE, (Z)-	-7002246
Hemp extract	-7000988	GROSSAMIDE	-7001678
Hemp extract	-7000988	GUAIOL	-7001579
Hemp extract	-7000988	HUMULENE	-7001984
Hemp extract	-7000988	IPSDIENOL	-7001827
Hemp extract	-7000988	KAEMPFEROL	-7001656
Hemp extract	-7000988	LIMONENE, (+/-)-	-7001618
Hemp extract	-7000988	LINALOOL, (+/-)-	-7002177
Hemp extract	-7000988	LINOLEIC ACID	-7001582
Hemp extract	-7000988	LINOLENIC ACID	-7001950
Hemp extract	-7000988	LUTEOLIN	-7001740
Hemp extract	-7000988	MYRCENE	-7001715
Hemp extract	-7000988	N-CAFFEOYLTYRAMINE	-7001876
Hemp extract	-7000988	OLEIC ACID	-7001759
Hemp extract	-7000988	ORIENTIN	-7001815
Hemp extract	-7000988	QUERCETIN	-7002182
Hemp extract	-7000988	TERPINOLENE	-7001953
Hemp extract	-7000988	TETRAHYDROCANNABIVARIN	-7002076
Hemp extract	-7000988	VITEXIN	-7001720
Hemp extract	-7000988	YLANGENE	-7001992 
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_const_of'
   and c1.concept_id = '-7001998'
;
/*
CANNABIDIOL	-7001998	Cannabis sativa[Cannabis sativa]	-7001415
CANNABIDIOL	-7001998	Marijuana[Cannabis sativa]	-7000192
CANNABIDIOL	-7001998	Hemp extract[Cannabis sativa]	-7000191
CANNABIDIOL	-7001998	Hemp[Cannabis sativa]	-7000190
CANNABIDIOL	-7001998	Da ma[Cannabis sativa]	-7000189
CANNABIDIOL	-7001998	Cannibidiol[Cannabis sativa]	-7000188
*/

select distinct c1.concept_name, c1.concept_id,  c3.concept_name, c3.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr1 on c1.concept_id = cr1.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr1.concept_id_2
     inner join staging_vocabulary.concept_relationship cr2 on c2.concept_id = cr2.concept_id_1
     inner join staging_vocabulary.concept c3 on c3.concept_id = cr2.concept_id_2
where cr1.relationship_id = 'napdi_is_const_of'
   and cr2.relationship_id = 'napdi_pt'
   and c1.concept_id = '-7001998'
;
/*
CANNABIDIOL	-7001998	Hemp extract	-7000988
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_spell_vr'
   and c1.concept_id = '-7000988'
;
/*
Hemp extract	-7000988	WHITE RECLUSE MARIJUANA FLOWERS	-7002875
Hemp extract	-7000988	WAX, MARIJUANA	-7002874
Hemp extract	-7000988	VAPORIZER CBD/MARIJUANA	-7002873
Hemp extract	-7000988	VAPORIZED MEDICINAL MARIJUANA	-7002872
Hemp extract	-7000988	VAPORIZED MARIJUANA	-7002871
Hemp extract	-7000988	VAPING MARIJUANA	-7002870
Hemp extract	-7000988	VAPE PEN (MARIJUANA)	-7002869
Hemp extract	-7000988	UNSPECIFIED MEDICAL MARIJUANA	-7002868
Hemp extract	-7000988	THC (PURE MARIJUANA IN PILL FORM) PRN	-7002867
Hemp extract	-7000988	THC MARIJUANA	-7002866
Hemp extract	-7000988	TETRAHYDROCANNABINOL (MARIJUANA,HASH)	-7002865
Hemp extract	-7000988	TASTY GUMMIES FULL SPECTRUM HEMP OIL ASSORTED FRUIT FLAVORS 40 GUMMIES	-7002864
Hemp extract	-7000988	R.L.V. 500MG HEMP EXTRACT ISOLATE	-7002863
Hemp extract	-7000988	RESET BIOSCIENCE BALANCE 300MG 99%+ NANO LIPOSOMAL ORGANIC HEMP CBD	-7002862
Hemp extract	-7000988	RECREATIONAL MARIJUANA	-7002861
Hemp extract	-7000988	QUEEN CITY HEMP CBD	-7002860
Hemp extract	-7000988	QUEEN CITY HEMP 500MG (CBD)	-7002859
Hemp extract	-7000988	PROCANNA--HEMP OIL	-7002858
Hemp extract	-7000988	POT	-7002857
Hemp extract	-7000988	PLUS CBD OIL HEMP DROPS PEPPERMINT EXTRA STRENGTH	-7002856
Hemp extract	-7000988	PLATINUM AND DANK BRAND MARIJUANA CARTRIDGES (VAPE THCNICOTINE)	-7002855
Hemp extract	-7000988	PEACE + WELLNESS ELEVATE (CBD/HEMP OIL INFUSED)	-7002854
Hemp extract	-7000988	PAX 3 VAPORIZER ^NOTHING WAS OBTAINED, BUT DRIED MARIJUANA WAS USED IN THE PAX 3 DEVICE	-7002853
Hemp extract	-7000988	ORIGINAL FORMULA HEMP EXTRACT OLIVE OIL FLAVOR CHARLOTTES WEB [CBD]	-7002852
Hemp extract	-7000988	ORIGINAL FORMULA HEMP EXTRACT OIL MINT CHOCOLATE FLAVOR CHARLOTTES WEB [CBD]	-7002851
Hemp extract	-7000988	OIL MARIJUANA	-7002850
Hemp extract	-7000988	NUTRITIONAL FRONTIERS FULL SPECTRUM HEMP EXTREME OIL (HEMP EXTRACT)	-7002849
Hemp extract	-7000988	METAGENICS, HEMP OIL BROAD SPECTRUM HEMP EXTRACT, 30ML BOTTLE WITH A 1	-7002848
Hemp extract	-7000988	MED MARIJUANA	-7002847
Hemp extract	-7000988	MEDICINAL RECREAT CANABIS HEMP F	-7002846
Hemp extract	-7000988	MEDICINAL MARIJUANA (CANNABIS SATIVA)	-7002845
Hemp extract	-7000988	MEDICINAL MARIJUANA	-7002844
Hemp extract	-7000988	MEDICAL MARIJUANA TINCTURE OILS	-7002843
Hemp extract	-7000988	MEDICAL MARIJUANA (RSO)	-7002842
Hemp extract	-7000988	MEDICAL MARIJUANA (PRESCRIPTION)	-7002841
Hemp extract	-7000988	MEDICAL MARIJUANA OIL	-7002840
Hemp extract	-7000988	MEDICAL MARIJUANA (MARIJUANA)	-7002839
Hemp extract	-7000988	MEDICAL MARIJUANA/CBD	-7002838
Hemp extract	-7000988	MEDICAL MARIJUANA CARD HOLDER	-7002837
Hemp extract	-7000988	MEDICAL MARIJUANA (CANNABIS SATIVA)	-7002836
Hemp extract	-7000988	MEDICAL MARIJUANA	-7002835
Hemp extract	-7000988	MEDICAL CANNABIS OIL VIOLET CBD	-7002834
Hemp extract	-7000988	MEDICAL CANNABIS CBD	-7002833
Hemp extract	-7000988	MAXIMUM STRENGTH HEMP EXTRACT OIL, 60MG/SERVING	-7002832
Hemp extract	-7000988	MARJUANA	-7002831
Hemp extract	-7000988	MARIJUNAN	-7002830
Hemp extract	-7000988	MARIJUNA	-7002829
Hemp extract	-7000988	MARIJUANNA	-7002828
Hemp extract	-7000988	MARIJUANA WAX	-7002827
Hemp extract	-7000988	MARIJUANA VAPING PRODUCT	-7002826
Hemp extract	-7000988	MARIJUANA VAPING LIQUID	-7002825
Hemp extract	-7000988	MARIJUANA VAPE	-7002824
Hemp extract	-7000988	MARIJUANA TINCTURE	-7002823
Hemp extract	-7000988	MARIJUANA/ THC/ HEMP/HASH	-7002822
Hemp extract	-7000988	MARIJUANA TBD HERB MEDICAL CENTER, 12509 OXNARD ST. N.HOLLYW	-7002821
Hemp extract	-7000988	MARIJUANA/SYNTHETIC PILL FORM OF MARIJUANA (CANNABIS SATIVA)	-7002820
Hemp extract	-7000988	MARIJUANA SUPPLEMENTS (THC AND CBD)	-7002819
Hemp extract	-7000988	MARIJUANA OIL VAPING	-7002818
Hemp extract	-7000988	MARIJUANA OIL	-7002817
Hemp extract	-7000988	MARIJUANA, N.O.S	-7002816
Hemp extract	-7000988	MARIJUANA (NO PREF. NAME)	-7002815
Hemp extract	-7000988	MARIJUANA (MEDICAL)	-7002814
Hemp extract	-7000988	MARIJUANA (MARIJUANA) UNKNOWN	-7002813
Hemp extract	-7000988	MARIJUANA(MARIJUANA)	-7002812
Hemp extract	-7000988	MARIJUANA (MARIJUANA)	-7002811
Hemp extract	-7000988	MARIJUANA             (MARIJUANA)	-7002810
Hemp extract	-7000988	MARIJUANA LIQUID	-7002809
Hemp extract	-7000988	MARIJUANA KUSH	-7002808
Hemp extract	-7000988	MARIJUANA HERB, THC OILS	-7002807
Hemp extract	-7000988	MARIJUANA HERB DEVICE USED: LOKEE BRAND VAPE^	-7002806
Hemp extract	-7000988	MARIJUANA GUMMIES	-7002805
Hemp extract	-7000988	MARIJUANA FOR MEDICAL USE	-7002804
Hemp extract	-7000988	MARIJUANA FLOWER	-7002803
Hemp extract	-7000988	MARIJUANA EXTRACT	-7002802
Hemp extract	-7000988	MARIJUANA EDIBLES	-7002801
Hemp extract	-7000988	MARIJUANA DROPS	-7002800
Hemp extract	-7000988	^MARIJUANA CREAM^	-7002799
Hemp extract	-7000988	MARIJUANA (CANNIBIS SATIVA)	-7002798
Hemp extract	-7000988	MARIJUANA (CANNIBAS)	-7002797
Hemp extract	-7000988	MARIJUANA (CANNABIS SATIVA) UNKNOWN	-7002796
Hemp extract	-7000988	MARIJUANA (CANNABIS SATIVA)(CANNABIS SATIVA)	-7002795
Hemp extract	-7000988	MARIJUANA (CANNABIS SATIVA) (CANNABIS SATIVA)	-7002794
Hemp extract	-7000988	MARIJUANA (CANNABIS SATIVA)	-7002793
Hemp extract	-7000988	MARIJUANA (CANNABIS SATIVA )	-7002792
Hemp extract	-7000988	MARIJUANA    (CANNABIS SATIVA)	-7002791
Hemp extract	-7000988	MARIJUANA (CANNABIS, CANNABIS SATIVA)	-7002790
Hemp extract	-7000988	MARIJUANA                   (CANNABIS, CANNABIS SATIVA)	-7002789
Hemp extract	-7000988	MARIJUANA (CANNABIS0	-7002788
Hemp extract	-7000988	MARIJUANA(CANNABIS)	-7002787
Hemp extract	-7000988	MARIJUANA (CANNABIS)	-7002786
Hemp extract	-7000988	MARIJUANA (CANNABIS,)	-7002785
Hemp extract	-7000988	MARIJUANA (CANNABIS, )	-7002784
Hemp extract	-7000988	MARIJUANA  (CANNABIS)	-7002783
Hemp extract	-7000988	MARIJUANA  (CANNABIS	-7002782
Hemp extract	-7000988	MARIJUANA                                        (CANNABIS)	-7002781
Hemp extract	-7000988	MARIJUANA	-7002780
Hemp extract	-7000988	MARIJUA	-7002779
Hemp extract	-7000988	MARIJAUNA	-7002778
Hemp extract	-7000988	MARIJANA	-7002777
Hemp extract	-7000988	MARIIJUANA	-7002776
Hemp extract	-7000988	MARIHUANA	-7002775
Hemp extract	-7000988	MARAJUANA	-7002774
Hemp extract	-7000988	LEGAL MARIJUANA	-7002773
Hemp extract	-7000988	LEGALIZED MARIJUANA	-7002772
Hemp extract	-7000988	K2/MARIJUANA	-7002771
Hemp extract	-7000988	ILLEGAL MARIJUANA	-7002770
Hemp extract	-7000988	HEMPZ	-7002769
Hemp extract	-7000988	HEMPWORX CBD OIL	-7002768
Hemp extract	-7000988	HEMPWORX	-7002767
Hemp extract	-7000988	HEMPVANA HEEL TASTIC	-7002766
Hemp extract	-7000988	HEMPVANA	-7002765
Hemp extract	-7000988	HEMPTRANCE NATURAL CBD GUMMIES	-7002764
Hemp extract	-7000988	HEMP SEED	-7002763
Hemp extract	-7000988	HEMPSEED	-7002762
Hemp extract	-7000988	HEMP PROTEIN POWDER	-7002761
Hemp extract	-7000988	HEMP MED EX: RSHO BLUE, GOLD	-7002760
Hemp extract	-7000988	HEMPLUCID CBD OIL 1000 MG VAPING	-7002759
Hemp extract	-7000988	HEMPANOL	-7002758
Hemp extract	-7000988	HASHISH	-7002757
Hemp extract	-7000988	EDIBLE MARIJUANA	-7002756
Hemp extract	-7000988	DRUG - MARIJUANA	-7002755
Hemp extract	-7000988	DISPENSARY MARIJUANA PLANT AND WAX CARTRIDGES.	-7002754
Hemp extract	-7000988	CYPRESS HEMP CBD OMEGAS	-7002753
Hemp extract	-7000988	CTFO (CHANGING THE FUTURE OUTCOME) 10XPURE CBD HEMP OIL	-7002752
Hemp extract	-7000988	COLESVAM MEDICAL MARIJUANA (CBD)	-7002751
Hemp extract	-7000988	CBD OIL (MEDICAL MARIJUANA)	-7002750
Hemp extract	-7000988	CBD OIL / HEMP OIL	-7002749
Hemp extract	-7000988	CBD OIL HEMP-DERIVED CANNABIDIOL FULL SPECTRUM HEMP SUPPL	-7002748
Hemp extract	-7000988	CBDISTILLERY 33MG CBD PER SERVING FULL SPECTRUM HEMP SUPPLEMENT	-7002747
Hemp extract	-7000988	CBD HEMP FLOWER (CBDTHC)	-7002746
Hemp extract	-7000988	CBD HEMP EXTRACT	-7002745
Hemp extract	-7000988	CBD EXTRACT	-7002744
Hemp extract	-7000988	CANNIMED OIL	-7002743
Hemp extract	-7000988	CANNIBIS	-7002742
Hemp extract	-7000988	CANNIBIDIOL OIL	-7002741
Hemp extract	-7000988	CANNIBAL	-7002740
Hemp extract	-7000988	CANNABIS VAPING	-7002739
Hemp extract	-7000988	CANNABIS TEA	-7002738
Hemp extract	-7000988	CANNABIS SPRAY	-7002737
Hemp extract	-7000988	CANNABIS SMOKING	-7002736
Hemp extract	-7000988	CANNABIS SATIVA SUBSP. SATIVA FLOWERING TOP	-7002735
Hemp extract	-7000988	CANNABIS SATIVA SUBSP. INDICA TOP	-7002734
Hemp extract	-7000988	CANNABIS SATIVA OIL	-7002733
Hemp extract	-7000988	CANNABIS SATIVA (HEMP HEARTS)	-7002732
Hemp extract	-7000988	CANNABIS SATIVA FLOWER	-7002731
Hemp extract	-7000988	CANNABIS SATIVA EXTRACT	-7002730
Hemp extract	-7000988	CANNABIS SATIVA	-7002729
Hemp extract	-7000988	CANNABIS RESIN	-7002728
Hemp extract	-7000988	CANNABIS LOTIONS	-7002727
Hemp extract	-7000988	CANNABIS INDICA SMOKE	-7002726
Hemp extract	-7000988	CANNABIS, INDICA	-7002725
Hemp extract	-7000988	CANNABIS INDICA	-7002724
Hemp extract	-7000988	CANNABIS BEDICA OLIE	-7002723
Hemp extract	-7000988	CANNABIS BEDICA	-7002722
Hemp extract	-7000988	CANNABIOL	-7002721
Hemp extract	-7000988	CANNABINIODS	-7002720
Hemp extract	-7000988	CANABIS LOTION	-7002719
Hemp extract	-7000988	CALM REST AND RELAX HEMP EXTRACT FORMULA (CBG)	-7002718
Hemp extract	-7000988	CALM REST AND RELAX HEMP EXTRACT FORMULA (CBD)	-7002717
Hemp extract	-7000988	AMOS HEMPS [CBD OIL IN MCT OIL]	-7002716
Hemp extract	-7000988	ALLERGY INJECTIONS - CAT DANDER,DOG DANDER,MOLD MIX,HEMP	-7002715
Hemp extract	-7000988	ADVANCED CBD OIL WITH TERPENES (FROM HEMP)	-7002714
Hemp extract	-7000988	ACETAMINOPHEN/CODINE MEDICAL MARIJUANA	-7002713
Hemp extract	-7000988	60MG, PLANT?BASED CANNABINOIDS PER 1ML HEMP EXTRACT MINT CHOCOLATE FLA	-7002712
Hemp extract	-7000988	2 HITS OF MARIJUANA	-7002711
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_spell_vr_of'
    and c1.concept_id = '-7002719'  
;
/*
CANABIS LOTION	-7002719	Hemp extract	-7000988
*/

---------------------------------------


-- Test using Licorice
select * from staging_vocabulary.concept c where concept_class_id in ('Licorice', 'Liquorice','Zhangguogancao');
/*
-7000424	Guang guo gan cao[Glycyrrhiza glabra]	NaPDI research	NAPDI	Liquorice		b005d419-55e7-42a8-aacc-b110d6f9d37d	2000-01-01	2099-02-22	
-7000425	Licorice[Glycyrrhiza glabra]	NaPDI research	NAPDI	Liquorice		b005d419-55e7-42a8-aacc-b110d6f9d37d	2000-01-01	2099-02-22	
-7000426	Liquorice[Glycyrrhiza glabra]	NaPDI research	NAPDI	Liquorice		b005d419-55e7-42a8-aacc-b110d6f9d37d	2000-01-01	2099-02-22	
-7000427	Licorice[Glycyrrhiza inflata]	NaPDI research	NAPDI	Zhangguogancao		59eda220-bbb5-4dc0-b57a-d010a4222c03	2000-01-01	2099-02-22	
-7000428	Zhangguogancao[Glycyrrhiza inflata]	NaPDI research	NAPDI	Zhangguogancao		59eda220-bbb5-4dc0-b57a-d010a4222c03	2000-01-01	2099-02-22	
-7000429	Chinese licorice[Glycyrrhiza uralensis]	NaPDI research	NAPDI	Licorice		dc6f848b-18bb-4620-81b1-b2af39721bd9	2000-01-01	2099-02-22	
-7000430	Gan cao[Glycyrrhiza uralensis]	NaPDI research	NAPDI	Licorice		dc6f848b-18bb-4620-81b1-b2af39721bd9	2000-01-01	2099-02-22	
-7000431	Licorice[Glycyrrhiza uralensis]	NaPDI research	NAPDI	Licorice		dc6f848b-18bb-4620-81b1-b2af39721bd9	2000-01-01	2099-02-22	
-7001254	Glycyrrhiza uralensis[Glycyrrhiza uralensis]	NaPDI research	NAPDI	Licorice		dc6f848b-18bb-4620-81b1-b2af39721bd9	2000-01-01	2099-02-22	
-7001276	Glycyrrhiza glabra[Glycyrrhiza glabra]	NaPDI research	NAPDI	Liquorice		b005d419-55e7-42a8-aacc-b110d6f9d37d	2000-01-01	2099-02-22	
-7001356	Glycyrrhiza inflata[Glycyrrhiza inflata]	NaPDI research	NAPDI	Zhangguogancao		59eda220-bbb5-4dc0-b57a-d010a4222c03	2000-01-01	2099-02-22	
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_pt'
   and c1.concept_id in (-7001254,-7001276,-7001356)
;
/*
Glycyrrhiza inflata[Glycyrrhiza inflata]	-7001356	Zhangguogancao	-7001199
Glycyrrhiza glabra[Glycyrrhiza glabra]	-7001276	Liquorice	-7001081
Glycyrrhiza uralensis[Glycyrrhiza uralensis]	-7001254	Licorice	-7001149
*/

select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_pt_of'
   and c1.concept_id = -7001149
;
/*
Licorice	-7001149	Glycyrrhiza uralensis[Glycyrrhiza uralensis]	-7001254
Licorice	-7001149	Licorice[Glycyrrhiza uralensis]	-7000431
Licorice	-7001149	Gan cao[Glycyrrhiza uralensis]	-7000430
Licorice	-7001149	Chinese licorice[Glycyrrhiza uralensis]	-7000429
*/

select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_has_const'
   and c1.concept_id = -7000430
;
/*
Gan cao[Glycyrrhiza uralensis]	-7000430	GLYCYRRHIZIN	-7002264
Gan cao[Glycyrrhiza uralensis]	-7000430	DEHYDROGLYASPERIN C	-7002235
Gan cao[Glycyrrhiza uralensis]	-7000430	3-O-METHYLGLYCYROL	-7002226
Gan cao[Glycyrrhiza uralensis]	-7000430	URALSAPONIN V	-7002175
Gan cao[Glycyrrhiza uralensis]	-7000430	LICORICESAPONIN A3	-7002147
Gan cao[Glycyrrhiza uralensis]	-7000430	DEHYDROGLYASPERIN D	-7002106
Gan cao[Glycyrrhiza uralensis]	-7000430	MEDICARPIN	-7002103
Gan cao[Glycyrrhiza uralensis]	-7000430	FORMONONETIN	-7002049
Gan cao[Glycyrrhiza uralensis]	-7000430	URALSAPONIN Y	-7002009
Gan cao[Glycyrrhiza uralensis]	-7000430	GLYASPERIN F	-7001966
Gan cao[Glycyrrhiza uralensis]	-7000430	LIQUIRITIN APIOSIDE	-7001965
Gan cao[Glycyrrhiza uralensis]	-7000430	LICORICESAPONIN G2	-7001957
Gan cao[Glycyrrhiza uralensis]	-7000430	URALSAPONIN F	-7001937
Gan cao[Glycyrrhiza uralensis]	-7000430	SEMILICOISOFLAVONE B	-7001905
Gan cao[Glycyrrhiza uralensis]	-7000430	LICORICIDIN	-7001885
Gan cao[Glycyrrhiza uralensis]	-7000430	ALLOLICOISOFLAVONE A	-7001865
Gan cao[Glycyrrhiza uralensis]	-7000430	LICORICONE	-7001861
Gan cao[Glycyrrhiza uralensis]	-7000430	GLYCYRIN	-7001845
Gan cao[Glycyrrhiza uralensis]	-7000430	URALSAPONIN C	-7001844
Gan cao[Glycyrrhiza uralensis]	-7000430	KANZONOL P	-7001831
Gan cao[Glycyrrhiza uralensis]	-7000430	URALSAPONIN X	-7001807
Gan cao[Glycyrrhiza uralensis]	-7000430	ISOGLYCYROL	-7001729
Gan cao[Glycyrrhiza uralensis]	-7000430	4',7-DIHYDROXYFLAVONE	-7001698
Gan cao[Glycyrrhiza uralensis]	-7000430	LICOISOFLAVONE B	-7001697
Gan cao[Glycyrrhiza uralensis]	-7000430	LIQUIRITIN	-7001667
Gan cao[Glycyrrhiza uralensis]	-7000430	LICORICESAPONIN E2	-7001666
Gan cao[Glycyrrhiza uralensis]	-7000430	JARANOL	-7001637
Gan cao[Glycyrrhiza uralensis]	-7000430	3-O-(.BETA.-D-GLUCURONOPYRANOSYL-(1->2)-.BETA.-D-GALACTOPYRANOSYL)GLYCYRRHETIC ACID	-7001634
Gan cao[Glycyrrhiza uralensis]	-7000430	TRIHYDROXYCHALCONE	-7001604
Gan cao[Glycyrrhiza uralensis]	-7000430	VESTITOL, (-)-	-7001592
Gan cao[Glycyrrhiza uralensis]	-7000430	URALSAPONIN W	-7001591
Gan cao[Glycyrrhiza uralensis]	-7000430	LIQUIRITIGENIN	-7001566
Gan cao[Glycyrrhiza uralensis]	-7000430	ISOTRIFOLIOL	-7001555
*/

select distinct c1.concept_name, c1.concept_id,  c3.concept_name, c3.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr1 on c1.concept_id = cr1.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr1.concept_id_2
     inner join staging_vocabulary.concept_relationship cr2 on c2.concept_id = cr2.concept_id_1
     inner join staging_vocabulary.concept c3 on c3.concept_id = cr2.concept_id_2
where cr1.relationship_id = 'napdi_is_pt_of'
   and cr2.relationship_id = 'napdi_has_const'
   and c1.concept_id = -7001149
;
/*
Licorice	-7001149	3-O-(.BETA.-D-GLUCURONOPYRANOSYL-(1->2)-.BETA.-D-GALACTOPYRANOSYL)GLYCYRRHETIC ACID	-7001634
Licorice	-7001149	3-O-METHYLGLYCYROL	-7002226
Licorice	-7001149	4',7-DIHYDROXYFLAVONE	-7001698
Licorice	-7001149	ALLOLICOISOFLAVONE A	-7001865
Licorice	-7001149	DEHYDROGLYASPERIN C	-7002235
Licorice	-7001149	DEHYDROGLYASPERIN D	-7002106
Licorice	-7001149	FORMONONETIN	-7002049
Licorice	-7001149	GLYASPERIN F	-7001966
Licorice	-7001149	GLYCYRIN	-7001845
Licorice	-7001149	GLYCYRRHIZIN	-7002264
Licorice	-7001149	ISOGLYCYROL	-7001729
Licorice	-7001149	ISOTRIFOLIOL	-7001555
Licorice	-7001149	JARANOL	-7001637
Licorice	-7001149	KANZONOL P	-7001831
Licorice	-7001149	LICOISOFLAVONE B	-7001697
Licorice	-7001149	LICORICESAPONIN A3	-7002147
Licorice	-7001149	LICORICESAPONIN E2	-7001666
Licorice	-7001149	LICORICESAPONIN G2	-7001957
Licorice	-7001149	LICORICIDIN	-7001885
Licorice	-7001149	LICORICONE	-7001861
Licorice	-7001149	LIQUIRITIGENIN	-7001566
Licorice	-7001149	LIQUIRITIN	-7001667
Licorice	-7001149	LIQUIRITIN APIOSIDE	-7001965
Licorice	-7001149	MEDICARPIN	-7002103
Licorice	-7001149	SEMILICOISOFLAVONE B	-7001905
Licorice	-7001149	TRIHYDROXYCHALCONE	-7001604
Licorice	-7001149	URALSAPONIN C	-7001844
Licorice	-7001149	URALSAPONIN F	-7001937
Licorice	-7001149	URALSAPONIN V	-7002175
Licorice	-7001149	URALSAPONIN W	-7001591
Licorice	-7001149	URALSAPONIN X	-7001807
Licorice	-7001149	URALSAPONIN Y	-7002009
Licorice	-7001149	VESTITOL, (-)-	-7001592 
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_const_of'
   and c1.concept_id = -7002264
;
/*
GLYCYRRHIZIN	-7002264	Glycyrrhiza inflata[Glycyrrhiza inflata]	-7001356
GLYCYRRHIZIN	-7002264	Glycyrrhiza uralensis[Glycyrrhiza uralensis]	-7001254
GLYCYRRHIZIN	-7002264	Licorice[Glycyrrhiza uralensis]	-7000431
GLYCYRRHIZIN	-7002264	Gan cao[Glycyrrhiza uralensis]	-7000430
GLYCYRRHIZIN	-7002264	Chinese licorice[Glycyrrhiza uralensis]	-7000429
GLYCYRRHIZIN	-7002264	Zhangguogancao[Glycyrrhiza inflata]	-7000428
GLYCYRRHIZIN	-7002264	Licorice[Glycyrrhiza inflata]	-7000427
*/

select distinct c1.concept_name, c1.concept_id,  c3.concept_name, c3.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr1 on c1.concept_id = cr1.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr1.concept_id_2
     inner join staging_vocabulary.concept_relationship cr2 on c2.concept_id = cr2.concept_id_1
     inner join staging_vocabulary.concept c3 on c3.concept_id = cr2.concept_id_2
where cr1.relationship_id = 'napdi_is_const_of'
   and cr2.relationship_id = 'napdi_pt'
   and c1.concept_id = -7002264
;
/*
GLYCYRRHIZIN	-7002264	Licorice	-7001149
GLYCYRRHIZIN	-7002264	Zhangguogancao	-7001199
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_spell_vr'
   and c1.concept_id = -7001149
;
/*
EMPTY B/C WE DID NOT INCLUDE LICORICE IN THE REFERENCE SET FOR SPRING 2022
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_spell_vr_of'
    and c1.concept_id =  
;
/*
EMPTY B/C WE DID NOT INCLUDE LICORICE IN THE REFERENCE SET FOR SPRING 2022
*/