Complicated Query - SQL Server Forums

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

SQL Server Development (2000)

Complicated Query

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2003-07-24 : 00:11:50

I have 4 tables:
empType, Emp, AccidentInfo, EmployeeAccident.

EmpType describes 3 types of employees that are prepopulated:
The attributes are:
TypeID,
Type.
TypeID Type
1 Deputy
2 Supervisor
3 Staffmember

The Emp table has many attributes but the ones that are important here are:
emPID,pk int identity
TypeID (foreign key from empType table)
fname,
lname,
orgcode

AccidentInfo has also has several attributes that involve accidents.
The important ones here are:
accidentId int pk,identity
dateofAccident datetime,
timeOfaccident datetime,
caller (person who called in when an accident occurs)
operator (person involved in an accident)
accidentNotes,
dateTimeLog (date and time accident was logged in)
Again there are more fields but I can field those in once I get my query right.

Finally, there is employeeAccident
The attributes are:
EmployAccidentID pk int identity,
empID (foreign key from emp table),
TrackingNumber (foreign key from accidentInfo table)
Completed bit (determines whether each employee (from emp and emptype tables have processed paperwork after an accident occurs, 0 for yes, 1 or No)

My problem is I need to create a summary report that can tie all of these together in one sql statement or multiple statement.
I am having problem join all of the tables to display:

TrackingNumber, DateofAccident,TimeOfAccident,Caller, Operator,Deputy,Supervisor,Staffmember, AccidentNotes, DateAndTimeLog,Completed.

If I use this query:
SELECT * FROM AccidentInfo, I get all records except employee type and their fnames and lnames (emptype and emp table) ,
Completed.

If I use this query:
SELECT theEmp.fname+' '+theEmp.lname as fullName, tblEmployeeType.Type,
EmployAccident.Completed
FROM EmployeeType, Emp, EmployAccident
WHERE EmployeeType.TypeID = Emp.TypeID AND
Emp.empID = EmployAccident.empID

I get the employee types (supervisor, deputy, staffmember) and their names (fname, lname), completed.
I need to somehow combine these tables into one query to be able to display all their records at one.

I am asking for your help/expertise, please.
I hope this is explained good enough.
Thanks in advance

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-24 : 03:16:33

SELECT Emp.fname+' '+Emp.lname as fullName, EmpType.Type as empType,
EmployeeAccident.Completed empCompleted, AccidentInfo.*

FROM EmpType, Emp, EmployeeAccident, AccidentInfo

WHERE EmpType.TypeID = Emp.TypeID AND

Emp.empID = EmployeeAccident.empID AND

EmployeeAccident.TrackingNumber=AccidentInfo.accidentID

- Vit

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2003-07-24 : 11:51:11

that's exactly what I am looking for.
Thank you very, very much, stoad!

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-24 : 12:05:56

quote:

FROM EmpType, Emp, EmployeeAccident, AccidentInfo

WHERE EmpType.TypeID = Emp.TypeID AND

Emp.empID = EmployeeAccident.empID AND

EmployeeAccident.TrackingNumber=AccidentInfo.accidentID

- Vit

Stoad, Stoad....

from emptype as a
inner join emp as b
on a.typeid = b.typeid
inner join employeeaccident as c
on b.empid = c.empid
inner join accidentinfo as d
on c.trackingnumber = d.accidentid

---------------
Shadow to Light

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-24 : 12:11:29

[blush]Thank you, my dear Amethystium!![/blush]

- Vit

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-24 : 12:15:20