asked on

Help with SQL Query

I have three tables:

TableAccident (ID, Date, County)
TableVehicle (ID, UnitNumber, VehicleType,
TablePerson (ID, UnitNumber, PersonNumber, Age)

TableAccident has an ID column that is unique and has a one to many relationship with TableVehicle and TablePerson.

For example, for a crash with 2 vehicles (one person in one vehicle and 3 in the other) the rows would look like the attached image.

I want to create a new table that has the following columns:
ID (unique)
Date
County
MotorcycleCrash ('Yes' if ANY of the vehicles involved are VehicleType='Motorcycle")
TeenCrash ('Yes' if [Age] is between 13 and 19 AND the PersonNumber=1 for any of the vehicles involved in crash -- otherwise 'No')
Screenshot-2.jpg

ASKER CERTIFIED SOLUTION

dsacker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

VAMS1

ASKER

dsacker -- This code is exactly what I'm looking for, though I am running into a problem when there are multiple Motorcycles involved in crash -- (e.g., TableVehicle has 1 car and 2 motorcyles).. when the query gets to this point, this error comes up:

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

It looks like it is trying to return 2 'Yes's, correct? Any idea how to get around this?

dsacker

I corrected the code, possibly after you grabbed it. Can you grab again and test?

Also, if you want to create a view from this, above the SELECT statement, simply add:

CREATE VIEW ViewAccidentStats AS

VAMS1

ASKER

Exactly what I needed! Thanks!