Error in CO04 - includes join between care_site_id and concept_id

[whitneyf11]

Query CO04 - Count In what place of service where condition diagnoses
Description: Returns the distribution of the visit place of service where the condition was reported.

SELECT
concept_name AS place_of_service_name,
place_freq
FROM
(SELECT
care_site_id,
count(*) AS place_freq
FROM
(SELECT
care_site_id
FROM
(SELECT
visit_occurrence_id
FROM cdm.condition_occurrence
WHERE condition_concept_id = 31967 -- Input condition
AND visit_occurrence_id IS NOT NULL
) AS from_cond
LEFT JOIN
(SELECT
visit_occurrence_id,
care_site_id
FROM cdm.visit_occurrence
) AS from_visit
ON from_cond.visit_occurrence_id=from_visit.visit_occurrence_id
) AS from_cond_visit
GROUP BY care_site_id
) AS place_id_count
LEFT JOIN
(SELECT
concept_id,
concept_name
FROM cdm.concept
) AS place_concept
ON place_id_count.care_site_id=place_concept.concept_id
ORDER BY place_freq;

The next to last line tries to join on care_site_id = concept_id. The following code was written by a colleague to correct this error.

SELECT
care_site_name,
place_freq
FROM
(SELECT
care_site_id,
count(*) AS place_freq
FROM
(SELECT
care_site_id
FROM
(SELECT
visit_occurrence_id
FROM cdm.condition_occurrence_f
WHERE condition_concept_id = 31967 -- Input condition
AND visit_occurrence_id IS NOT NULL
) AS from_cond
LEFT JOIN
(SELECT
visit_occurrence_id,
care_site_id
FROM cdm.visit_occurrence_f
) AS from_visit
ON from_cond.visit_occurrence_id=from_visit.visit_occurrence_id
) AS from_cond_visit
GROUP BY care_site_id
) AS place_id_count
LEFT JOIN
(SELECT
care_site_id,
care_site_name
FROM cdm.care_site_f
) AS place_name
ON place_id_count.care_site_id=place_name.care_site_id
ORDER BY place_freq;

I have made more drastic changes to the code. The code provided returns information about the care site, but the title and descriptive info for the query talks about place of service. Please see the following code for returning info about place of service.

SELECT
concept_name AS place_of_service_name,
count(*) AS place_freq
FROM
(SELECT
care_site_id
FROM
(SELECT
care_site_id
FROM
(SELECT
visit_occurrence_id
FROM cdm.condition_occurrence
WHERE condition_concept_id = 31967 -- Input condition
AND visit_occurrence_id IS NOT null
) AS from_cond
LEFT JOIN
(SELECT
visit_occurrence_id,
care_site_id
FROM cdm.visit_occurrence
) AS from_visit
ON from_cond.visit_occurrence_id=from_visit.visit_occurrence_id
) AS from_cond_visit
GROUP BY care_site_id
) AS place_id_count
LEFT JOIN
(SELECT
care_site_id,
place_of_service_concept_id
FROM cdm.care_site
) AS collect_place_concept
ON place_id_count.care_site_id=collect_place_concept.care_site_id
left join
(SELECT
concept_id,
concept_name
FROM cdm.concept
) AS place_concept
ON collect_place_concept.place_of_service_concept_id=place_concept.concept_id
group by place_concept.concept_name
ORDER BY place_freq;

I am not an expert, so please feel free to provide feedback. Thank you!