December 3, 2007 at 10:02 am
With that last statement it seems to me like you want this.
-- This will give you all SMs where Disease S has occurred regardless of SC occurring.
SELECT
I.SM as Disease_S
FROM
dbo.Indicators_for_regions_Both iI
INNER JOIN
dbo.Region_lookup R
INNER JOIN
dbo.Gov_regions_mining_score_TEMP G
ON
G.Region_key = R.Number_count
ON
RTRIM(R.String) = RTRIM(iI.Region)
WHERE
RTRIM(iI.Disease_type) LIKE 'Mortality from S'
AND G.Total_coal_mining_score <= 3
-- This will give you all SMs where Disease SC has occurred regardless of S occurring.
SELECT
I.SM as Disease_SC
FROM
dbo.Indicators_for_regions_Both iI
INNER JOIN
dbo.Region_lookup R
INNER JOIN
dbo.Gov_regions_mining_score_TEMP G
ON
G.Region_key = R.Number_count
ON
RTRIM(R.String) = RTRIM(iI.Region)
WHERE
RTRIM(iI.Disease_type) LIKE 'Mortality from SC'
AND G.Total_coal_mining_score <= 3
December 3, 2007 at 10:04 am
That is very similar to what I started with. I'll go back to your complex code then.
December 3, 2007 at 10:09 am
If you want one resultset with both values against each other then I assume SM is unique in Indicators_for_regions_Both so you could do this to have together as one set.
SELECT
A.Disease_S,
B.Disease_SC
FROM
(
-- This will give you all SMs where Disease S has occurred regardless of SC occurring.
SELECT
I.SM as Disease_S
FROM
dbo.Indicators_for_regions_Both iI
INNER JOIN
dbo.Region_lookup R
INNER JOIN
dbo.Gov_regions_mining_score_TEMP G
ON
G.Region_key = R.Number_count
ON
RTRIM(R.String) = RTRIM(iI.Region)
WHERE
RTRIM(iI.Disease_type) LIKE 'Mortality from S'
AND G.Total_coal_mining_score <= 3
) A
FULL OUTER JOIN
(
-- This will give you all SMs where Disease SC has occurred regardless of S occurring.
SELECT
I.SM as Disease_SC
FROM
dbo.Indicators_for_regions_Both iI
INNER JOIN
dbo.Region_lookup R
INNER JOIN
dbo.Gov_regions_mining_score_TEMP G
ON
G.Region_key = R.Number_count
ON
RTRIM(R.String) = RTRIM(iI.Region)
WHERE
RTRIM(iI.Disease_type) LIKE 'Mortality from SC'
AND G.Total_coal_mining_score <= 3
) B
ON
A.Disease_S = B.Disease_SC
Which will give you in a recordset all values from disease S and all from disease SC, where they both happen the value exists in both columns, where only one happens the other column will be null.
December 3, 2007 at 10:10 am
With what you have just written here, if I select the region as well as the SM figure, for each case. I could then do an inner join to exclude regions that have one figure but not the other. The SM figures would then correspond with regard to their place in the array.
December 3, 2007 at 10:20 am
Be carefull it is very easy to overcomplicate a task when you are still trying to understand it.
Here are my assumptions
1) Table Relationships
Gov_regions_mining_score_TEMP to Region_lookup via Gov_regions_mining_score_TEMP.Region_key = Region_lookup.Number_count
Region_lookup to Indicators_for_regions_Both via Region_lookup.String = Indicators_for_regions_Both.Region
2) In one result you want to know the SM value from Indicators_for_regions_Both where Disease_type is 'Mortality from S'
In the other you want to know the SM value from Indicators_for_regions_Both where Disease_type is 'Mortality from SC'
3) You want only locations where Gov_regions_mining_score_TEMP has a Total_coal_mining_score <= (less than or equal to) 3 which is why you have to join to those two tables.
Another way could be this with the last way I did it. (Assuming SM is unique)
SELECT
MAX(CASE WHEN RTRIM(iI.Disease_type) = 'Mortality from S' THEN I.SM ELSE NULL Disease_S,
MAX(CASE WHEN RTRIM(iI.Disease_type) = 'Mortality from SC' THEN I.SM ELSE NULL Disease_SC,
FROM
dbo.Indicators_for_regions_Both I
INNER JOIN
dbo.Region_lookup R
INNER JOIN
dbo.Gov_regions_mining_score_TEMP G
ON
G.Region_key = R.Number_count
ON
RTRIM(R.String) = RTRIM(iI.Region)
WHERE
(
RTRIM(iI.Disease_type) LIKE 'Mortality from S'
OR RTRIM(iI.Disease_type) LIKE 'Mortality from SC'
)
AND G.Total_coal_mining_score <= 3
GROUP BY
I.SM
Should com out the same as my last query. There are many ways a query can be written, it is a matter of the expected outcome that drives it.
December 3, 2007 at 2:25 pm
Can anybody give me one simple reason for those RTRIM functions you guys put everywhere?
_____________
Code for TallyGenerator
December 3, 2007 at 2:40 pm
Sergiy (12/3/2007)
Can anybody give me one simple reason for those RTRIM functions you guys put everywhere?
I did it becuase the original poster did just to be sure I didn't cause unexpected results not knowning about his data.
December 3, 2007 at 3:50 pm
Any chance it can cause?
_____________
Code for TallyGenerator
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply