Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Results that violate criterion

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-11 : 10:58:38
Roy writes "I'm baffled by the results of the following query:
________________________________________________________
select events.[report number], [microfilm number], [crash date]
into lowspeed
from events inner join vehicles
on events.[report number] = vehicles.[report number]
where [dummy record] = 'N'
and [estimated mph] between 1 and 10
and ([1st harmful event] = 01
or [2nd harmful event] = 01)
and [type of vehicle] in
('01','02','03','04','05','06','07','08','10','11','12')
and [crash injury severity] = 5

select events.[report number], [microfilm number], [crash date]
into notlowspeed
from events inner join vehicles
on events.[report number] = vehicles.[report number]
where [dummy record] = 'N'
and [estimated mph] > 10
and ([1st harmful event] = 01
or [2nd harmful event] = 01)
and [type of vehicle] in
('01','02','03','04','05','06','07','08','10','11','12')
and [crash injury severity] = 5

select distinct [report number], [microfilm number], [crash date]
into truelowspeed
from lowspeed where [report number] not in (select [report number] from
notlowspeed)

select [report number]
into pedtable
from pedestrians

select [report number],[microfilm number], [crash date]
as [Lowspeed Fatal Crashes - 1994]
from truelowspeed
where [report number] not in (select [report number] from pedtable)

drop table pedtable
drop table truelowspeed
drop table lowspeed
drop table notlowspeed
_______________________________________________

Let me explain what I'm trying to do.

In the first temp table "lowspeed," I'm trying to lump all crashes where a vehicle was doing between 1 and 10 mph.
In "notlowspeed," I'm setting up a table of crashes in which at least one vehicle was going over 10 mph.
By the third gyration, I'm trying to generate "truelowspeed;" which I thought would contain only crashes in which all of the criterion in the first two queries were satisfied and all of the vehicles involved in the crashes were doing between 1 and 10 mph (NO vehicles exceeding the 1 to 10 mph parameter).
The "pedtable" maneuver is simply to pull out any of these crashes in which a pedestrian was involved.
What I'm getting is a lot of crashes in which I have dummy records, several crashes in which one of the vehicles was going over 10 mph, and types of vehicles involved in the crash that should have been excluded by the criterion I specified.

Thoroughly stumped. Would be most grateful for any kind insight/advice."

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-11 : 13:26:08
Can you show some data?

I presume that report number is the primary key for all this - in which case it is a waste of space including [microfilm number], [crash date] in your tables.
Your table design also looks a bit dubious.

If you just look at the data in the intermediate tables you should be able to see why you are not fitering out records.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

ToddV
Posting Yak Master

218 Posts

Posted - 2002-03-11 : 14:13:53
Roy,
I am pretty sure you can do this all in one query.

This will get you started:

select events.[report number], [microfilm number], [crash date]
from events
inner join vehicles on events.[report number] = vehicles.[report number]
LEFT JOIN pedestrians ON pedestrians.[report number] = events.[report number]
WHERE [dummy record] = 'N' and pedestrians.[report number] IS NULL
GROUP BY events.[report number], [microfilm number], [crash date]
HAVING MAX([estimated mph]) between 1 and 10


you would have to give some more info about those other columns for me to decide whether to express the requirements associated with them in the where or having clause, or if we need to go some other way entirely. Posting some test data would help. Post both what is in the database and what the expected result is.

Go to Top of Page
   

- Advertisement -