Exporting a concept set: Concept Identifier List vs. Included Concept Identifier List

Vocabulary Users
1

Hello! If we are trying to use our concept set in an SQL query that we have written and are using to manually query our database (i.e. not using the webAPI), should be copying our “Concept Identifier List” or our “Included Concept Identifier List” into our SQL query. For example:

Select patient_id From concept_occurrence where concept_id in (Concept Identifier List)

vs.

Select patient_id From concept_occurrence where concept_id in (Included Concept Identifier List)

My issue is arising because I tried to use “Included Concept Identifier List” but with 68K included concept IDs (I need to include all descendants in a medication list), the list is too long for SQL to process the text string.

Thanks!

2

Can you put the values in a table? Call it Included_Concept_Identifier_List. Then you can query it

Select patient_id From concept_occurrence where concept_id in (select concept_id from Included_Concept_Identifier_List)

or in a join

Select patient_id
From concept_occurrence
inner join Included_Concept_Identifier_List
on concept_occurrence.concept_id = Included_Concept_Identifier_List.concept_id

3

Thanks @roger.carlson! I will try that. Also, one more follow up. If I am using ingredient concept_ids to select medications, will all descendants of the ingredient concept_ids be included in results if I simply use:

where concept_id in (ingredient concept_id list)

or do I have to explicitly include all the concept_ids for the ingredients as well as their descendants?

Thanks again!

4

And if modifying the tables in the database are not an option, is there another way to find all descendents in the join? Thanks!

5

I’m not entirely sure I understand, but it depends upon the software that you’re using to query. If it has the capabilities of referencing a text file as a table or of loading a text file into a temporary table, then you can still use it in the way I described.

For instance, Microsoft Access can “link” to a text file and see it as a table. You can use this table just as you would any table.