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.
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 lowspeedfrom events inner join vehicleson 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] = 5select events.[report number], [microfilm number], [crash date]into notlowspeedfrom events inner join vehicleson 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] = 5select distinct [report number], [microfilm number], [crash date]into truelowspeedfrom lowspeed where [report number] not in (select [report number] fromnotlowspeed)select [report number]into pedtablefrom pedestriansselect [report number],[microfilm number], [crash date]as [Lowspeed Fatal Crashes - 1994]from truelowspeedwhere [report number] not in (select [report number] from pedtable)drop table pedtabledrop table truelowspeeddrop table lowspeeddrop 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. |
|
|
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 NULLGROUP 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. |
|
|
|
|
|
|
|