December 3, 2007 at 7:31 am
Please can someone tell me if the following looks reasonable? I know that it is not reasonable as it doesn't give me what I want. I'm not sure how to put what I want into English. Bit complex. I'll try to put it into code again.
DECLARE @Number_count AS INT
DECLARE @max_number_count AS INT
SET @Number_count = 1
SET @max_number_count = (SELECT MAX(Number_count) FROM Region_lookup)
WHILE (@Number_count <= @max_number_count) BEGIN
SELECT I.SM
FROM Indicators_for_regions_Both I, Gov_regions_mining_score_TEMP G, Region_lookup R
WHERE EXISTS
(SELECT I.SM
FROM Indicators_for_regions_Both I, Gov_regions_mining_score_TEMP G, Region_lookup R
WHERE RTRIM(I.Disease_type) LIKE 'Mortality from S' AND
G.Total_coal_mining_score <= 3 AND G.Region_key = @Number_count AND
RTRIM(R.String) = RTRIM(I.Region))
AND RTRIM(I.Disease_type)
LIKE 'Mortality from SC' AND G.Total_coal_mining_score <= 3 AND
G.Region_key = @Number_count AND RTRIM(R.String) = RTRIM(I.Region)
ORDER BY I.Entry_key
SET @Number_count = @Number_count +1
END
December 3, 2007 at 7:43 am
At first glance I was going to say use a numbers or tally table. Do a search on this site and you'll find a couple of good articles on it.
But after looking at your code closer. What are you trying to accomplish here? You're selecting from the Cartesian product of three tables (no real biggie) but you're using the same three tables in the EXISTS part of the where clause. So it's really not obvious (to me) what you're trying to get done here.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 3, 2007 at 8:11 am
INSERT INTO #Temp_table_A
SELECT *
FROM Indicators_for_regions_Both I, Gov_regions_mining_score_TEMP G, Region_lookup R
WHERE RTRIM(I.Disease_type) LIKE 'Mortality from S' AND
G.Total_coal_mining_score <= 3 AND G.Region_key = R.Number_count AND
RTRIM(R.String) = RTRIM(I.Region)
INSERT INTO #Temp_table_B
SELECT *
FROM Indicators_for_regions_Both I, Gov_regions_mining_score_TEMP G, Region_lookup R
WHERE RTRIM(I.Disease_type)
LIKE 'Mortality from SC' AND G.Total_coal_mining_score <= 3 AND
G.Region_key = R.Number_count AND RTRIM(R.String) = RTRIM(I.Region)
ORDER BY I.Entry_key
SELECT A.SM FROM #Temp_table_A A, #Temp_table_B
WHERE A.Region = B.Region
AND RTRIM(A.Disease_type) LIKE 'Mortality from S'
AND RTRIM(B.Disease_type) LIKE 'Mortality from SC'
AND A.SM > 0
AND B.SM > 0
ORDER BY A.Region
SELECT B.SM FROM #Temp_table_A A, #Temp_table_B
WHERE A.Region = B.Region
AND RTRIM(A.Disease_type) LIKE 'Mortality from S'
AND RTRIM(B.Disease_type) LIKE 'Mortality from SC'
AND A.SM > 0
AND B.SM > 0
ORDER BY B.Region
December 3, 2007 at 8:34 am
Comments:
- I don't know how big these temp tables are, but if they're anything more than a few records, you should at least consider indexing the temp tables, so that your joins will work faster.
- you really should start using JOIN syntax.
- A like without a wildcard is an exact value match - use =. It makes your syntax more readable.
- Also - since you've already extracted the "S" data and the "SC" data based on 'Mortality from S' and 'Mortality from SC', there's no use in including them again in subsequent queries.
- You don't seem to be relating I to any table - why?
- If you only want to see records where SM>0, why not incorporate that into the initial select INTO query?
- don't use an ORDER BY in an INSERT statement.
- Finally - you're going to get the SAME list of SM's from A as you are from B, since you're doing an inner join and applying the same criteria. Why do you need it twice?
Try this:
INSERT INTO #Temp_table_A
SELECT *
FROM
Indicators_for_regions_Both I,
Gov_regions_mining_score_TEMP G,
Region_lookup R
WHERE
RTRIM(I.Disease_type) = 'Mortality from S' AND
G.Total_coal_mining_score <= 3 AND
G.Region_key = R.Number_count AND
RTRIM(R.String) = RTRIM(I.Region)
and SM>0 --don't know what table you got that from...
INSERT INTO #Temp_table_B
SELECT *
FROM
Indicators_for_regions_Both I,
Gov_regions_mining_score_TEMP G,
Region_lookup R
WHERE
RTRIM(I.Disease_type) = 'Mortality from SC' AND
G.Total_coal_mining_score <= 3 AND
G.Region_key = R.Number_count AND
RTRIM(R.String) = RTRIM(I.Region)
and SM>0 --don't know what table you got that from...
--again - if this is only a few records, this is likely overkill
Create index ix_B on #Temp_table_B(REGION,SM)
Create index ix_A on #Temp_table_A(REGION,SM)
SELECT
A.SM
FROM
#Temp_table_A A inner join
#Temp_table_B on A.Region = B.Region
ORDER BY A.Region
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 3, 2007 at 8:54 am
Thanks Matt for your code. One point. I'm not going to get the same result in the returns as your last point says. I'm going to get the same number of returns but the returns will be different.
Also, is there an easy way to get around the:
Invalid object name '#Temp_table_A'
error?
December 3, 2007 at 9:08 am
Where are you getting it? The only place I can think you'd be getting this would be line one, and that's only if you hadn't previously created the temporary table in this session.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 3, 2007 at 9:11 am
Reading your original query I am trying to understand what your goal is and the relationship between tables.
I only see one relationship
Indicators_for_regions_Both to Region_lookup on RTRIM(Region_lookup.String) = RTRIM(Indicators_for_regions_Both.Region)
But see no defining relationship for Gov_regions_mining_score_TEMP to anything.
The way you have in the original you would get a potential join of the first two tables then all rows from that reporduced against each record in the third (a cartisian product). So if the first two produce say 1,000 rows and the third table has 10 rows your resulting set is 10,000 rows. Is this the intent?
Also, from that what is your actual goal. You might simply have over worked your design becuase you didn't understand your goal ahead of time.
December 3, 2007 at 9:20 am
I'm now working with code that looks like: (but still getting error messages).
if exists (select 1 from INFORMATION_SCHEMA.tables where table_name = Temp_table_A) DROP TABLE Temp_table_A
CREATE TABLE [Temp_table_A] (
[Entry_key] [int] NOT NULL ,
[Disease_type] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Region_type] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Region] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Observed] [int] NOT NULL ,
[SM] [int] NOT NULL ,
[Lower_CI] [int] NOT NULL ,
[Upper_CI] [int] NOT NULL
) ON [PRIMARY]
if exists (select 1 from INFORMATION_SCHEMA.tables where table_name = Temp_table_B) DROP TABLE Temp_table_B
CREATE TABLE [Temp_table_B] (
[Entry_key] [int] NOT NULL ,
[Disease_type] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Region_type] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Region] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Observed] [int] NOT NULL ,
[SM] [int] NOT NULL ,
[Lower_CI] [int] NOT NULL ,
[Upper_CI] [int] NOT NULL
) ON [PRIMARY]
INSERT INTO Temp_table_A ([Entry_key], [Disease_type], [Region_type], [Region], [Observed], [SM], [Lower_CI], [Upper_CI])
SELECT *
FROM
Indicators_for_regions_Both I,
Gov_regions_mining_score_TEMP G,
Region_lookup R
WHERE
RTRIM(I.Disease_type) = 'Mortality from S' AND
G.Total_coal_mining_score <= 3 AND
G.Region_key = R.Number_count AND
RTRIM(R.String) = RTRIM(I.Region)
-- and I.SM > 0 --don't know what table you got that from...
INSERT INTO Temp_table_B ([Entry_key], [Disease_type], [Region_type], [Region], [Observed], [SM], [Lower_CI], [Upper_CI])
SELECT *
FROM
Indicators_for_regions_Both I,
Gov_regions_mining_score_TEMP G,
Region_lookup R
WHERE
RTRIM(I.Disease_type) = 'Mortality from SC' AND
G.Total_coal_mining_score <= 3 AND
G.Region_key = R.Number_count AND
RTRIM(R.String) = RTRIM(I.Region)
-- and I.SM > 0 --don't know what table you got that from...
--again - if this is only a few records, this is likely overkill
Create index ix_B on Temp_table_B(REGION,SM)
Create index ix_A on Temp_table_A(REGION,SM)
SELECT
A.SM
FROM
Temp_table_A A inner join
Temp_table_B B on A.Region = B.Region
ORDER BY A.Region
SELECT
B.SM
FROM
Temp_table_A A inner join
Temp_table_B B on B.Region = A.Region
ORDER BY B.Region
UPDATE:
I've update the if exists sections above
December 3, 2007 at 9:31 am
Mark,
my experience is, that if I can't put what I need into words, it means I don't understand it well enough. Trying to describe the problem as accurately as possible often helps to solve it. I've had this happen several times - I was stuck on some problem, until I tried to explain it to other people - then suddenly I realized what has to be done.
Try to explain to us what is this all about : what are the tables for, what do you want to do and why, give us an example of data and desired result. I'm sorry, but I really don't know how to help if I have no idea what has to be done.
December 3, 2007 at 9:34 am
Mark - your error is coming from you not putting quotes in this statement:
if exists (select 1
from INFORMATION_SCHEMA.tables
where table_name = 'Temp_table_A' --In this case temp_table_A is a string, so put qutoes around it
) DROP TABLE Temp_table_A
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 3, 2007 at 9:46 am
Looks to me you are looking at goverment regions where the Total_coal_mining_score <= 3 and based on that you want to know what regions have mortality issues related to one of the following:
Mortality from S
Mortality from SC
So it might still need a bit of tweaking but I think this is what you are looking for.
[Code]
SELECT
I.SM
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(I.Region)
WHERE
RTRIM(I.Disease_type) IN ('Mortality from S','Mortality from SC')
AND G.Total_coal_mining_score <= 3
[/code]
Now however looking at the code it might be constured that you only want locations where both occurred
Mortality from S
Mortality from SC
If so then this is probably what you are after.
[Code]
SELECT
I.SM
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(I.Region)
WHERE
(
RTRIM(I.Disease_type) LIKE 'Mortality from S'
OR RTRIM(I.Disease_type) LIKE 'Mortality from SC'
)
AND G.Total_coal_mining_score <= 3
GROUP BY
I.SM
HAVING
COUNT(DISTINCT I.Disease_type) = 2
[/code]
Some example data with an example of the outcome of the results would be helpful.
December 3, 2007 at 9:48 am
I wish to return two lists of rates (SM)s. The first list is specific to disease A and the second list is specific to disease B. These lists of figures will be used to produce correlation plots. Therefore only want to return figures if there are corresponding figures for each geographical region. This is because a single geographical region may have no death from cause A and therefore return no result but have deaths from cause B and therefore return a result. This is a problem because the two arrays then don't have corresponding figures that can be used in a correlation plot. Where there are no deaths from a single cause the data is not represented in the table and this is the problem. Perhaps it is the tables going into the process that are not sufficiently complete.
December 3, 2007 at 9:53 am
It looks like this could be along the right lines. Are you suggesting that I run this code twice, so that I get the figures seperately for S and SC?
December 3, 2007 at 9:54 am
Or as I suspect after thinking a hair more you want All SM's from regions that have experieced both Disease Types then this may actually be your item.
SELECT
I.SM
FROM
dbo.Indicators_for_regions_Both I
INNER JOIN
(
SELECT
RTRIM(I.Region) Region
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'
OR RTRIM(iI.Disease_type) LIKE 'Mortality from SC'
)
AND G.Total_coal_mining_score <= 3
GROUP BY
RTRIM(iI.Region)
HAVING
COUNT(DISTINCT iI.Disease_type) = 2
) X
ON
RTRIM(I.Region) = RTRIM(X.Region)
December 3, 2007 at 10:00 am
Your right. You don't want to group by SM. But your solution looks increadibly complex. Can't you break it up. It looks too complex to be correct.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply