Joins

I want to convert the following statement:
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code, M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec_SPLIT C,
Geo_mortality_count_SPLIT M,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
into something like this:
SELECT /*O.Geo_category_class,*/ E.Region, O.Instance_name, C.Disease_cat,
C.Constraint_ref, M.Sex, C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec_SPLIT C
JOIN Geo_mortality_count_SPLIT M
ON C.ICD_code = M.ICD AND M.Cause BETWEEN C.ICD_start AND
C.ICD_end_adjusted AND M.Output_Area = O.Output_Area AND
C.Sex_code_conversion = M.Sex
JOIN Experimental_parameters E
ON O.Geo_category_class = E.Region
WHERE E.Region = @.region AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY E.Region, O.Instance_name, C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
But this does not work. I am trying to produce the same join as the first,
except that I want the outer join with the Geo_definitions_normalised table
--
Message posted via http://www.sqlmonster.comSorry, please replace the first statement with:
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec_SPLIT C,
Geo_mortality_count_SPLIT M,
Geo_definitions_normalised O,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
--
Message posted via http://www.sqlmonster.com