search

ImagingDataCommons / ETL

(CORE REPO)
Apache License 2.0
0 stars 1 forks source link

dicom_patient_id appears to be missing in several clinical tables #39

Open fedorov opened 1 year ago

fedorov commented 1 year ago 
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.spie_aapm_lung_ct_challenge_clinical

head_neck_pet_ct_clinical

ispy2_clinical

Full UNIION query that led to this discovery (no, I did not type it manually):

SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_6698_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_A0
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_A1
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_A3
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_AA
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_AB
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_AC
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_AF
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_AG
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_BA
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_BQ
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_BR
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_CA
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_CJ
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_CK
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_E3
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_FS
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_FT
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_HE
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_I1
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_IC
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_IE
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_IM
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_IS
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_JS
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_JT
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_LP
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_PF
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_PH
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_PM
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_PO
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_PR
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_RT
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_SB
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_TS
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_VT
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_VY
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_A0
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_A1
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_AE
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_AI
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_AT
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_BS
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_DE
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_DS
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_EX
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_F1
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_MC
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_MH
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_MI
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_MR
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_MS
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_PQ
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_PR
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_SA
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_SC
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_T4
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_TA
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_V1
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_V2
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_V3
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_V4
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_VA
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_BL
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_BX
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_C2
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_DS
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_EX
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_F1
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_I1
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_IM
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_M4
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_P3
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_PL
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_PM
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_PR
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_PV
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_S1
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_S2
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_T0
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_TA
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_TD
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_hnscc_fdg_pet_ct_UP
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_A0
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_A1
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_AE
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_DS
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_F1
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_F2
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_I1
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_IM
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_O1
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_PR
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_Q2
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_QA
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_QZ
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_SS
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_T1
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_TA
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_TF
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.b_mode_and_ceus_liver_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.breast_diagnosis_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.breast_mri_nact_pilot_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.c4kc_kits_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.covid_19_ar_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.covid_19_ar_imaging
#UNION ALL
#SELECT
#  dicom_patient_id
#FROM
#  idc-dev.idc_v11_clinical.head_neck_pet_ct_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.head_neck_radiomics_hn1_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.hnscc_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.hnscc_radiomics
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.hnscc_3dct_rt_outcome
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.hnscc_3dct_rt_weight
#UNION ALL
#SELECT
#  dicom_patient_id
#FROM
#  idc-dev.idc_v11_clinical.ispy2_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.lidc_idri_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.lung_pet_ct_dx_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.midrc_ricord_1a_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.midrc_ricord_1b_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.nsclc_radiomics_genomics_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.nsclc_radiomics_interobserver1_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.opc_radiomics_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.qin_headneck_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.rembrandt_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.soft_tissue_sarcoma_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.cptac_brca_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.cptac_ccrcc_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.cptac_coad_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.cptac_gbm_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.cptac_hnscc_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.cptac_lscc_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.cptac_luad_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.cptac_pda_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.cptac_ucec_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_acc_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_blca_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_brca_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_cesc_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_chol_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_coad_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_dlbc_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_esca_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_hnsc_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_kich_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_kirc_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_kirp_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_lgg_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_lihc_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_luad_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_lusc_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_meso_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_ov_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_paad_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_pcpg_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_prad_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_read_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_sarc_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_skcm_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_stad_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_tgct_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_thca_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_thym_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_ucec_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_ucs_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_uvm_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.cmmd_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.covid_19_ny_sbu_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.hcc_tace_seg_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.hnscc_3dct_rt_demographics
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.hnscc_3dct_rt_dose
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.ispy1_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.ispy1_outcome
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.midrc_ricord_1c_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.nsclc_radiomics_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.nsclc_radiogenomics_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.prostate_diagnosis_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.qin_breast_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.soft_tissue_sarcoma_outcome
#UNION ALL
#SELECT
#  dicom_patient_id
#FROM
#  idc-dev.idc_v11_clinical.spie_aapm_lung_ct_challenge_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.cptac_ov_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.tcga_gbm_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.lung_fused_ct_pathology_clinical
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_TX
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_LE
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_V1
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_V2
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_flt_breast_V3
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_CO
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_fmiso_brain_FM
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_SF
UNION ALL
SELECT
  dicom_patient_id
FROM
  idc-dev.idc_v11_clinical.acrin_nsclc_fdg_pet_ST
G-White-ISB commented 1 year ago

Missing dicom_patient_id in the 3 collections spie_aapm_lung_ct_challenge_clinical, head_neck_pet_ct_clinical, ispy2_clinical are now added. This can be an issue because the clinical_notes.json file has to record information about how to map patientids between the clinical files and the dicom files.

But I can add a check to make sure each table has the dicom_patient_id column at creation

fedorov commented 1 year ago

But I can add a check to make sure each table has the dicom_patient_id column at creation

Yes, this would be great!

So did this happen because you just didn't add that mapping manually in the notes?