SQL - Best way to do this?

Endurance Man

I have 3 tables - Events, People, PTypes.

Events has a PeopleID field, the PeopleID comes from the People table. The People has a PTYPEID field, this PTYPEID comes from the PTypes table.

There are three different PTypes, 1- Staff, 2- Visitor, 3- Inmate. These descriptions are held in PTypes under a description field, and referenced as 1, 2 or 3 around the database as PTYPEID.
I need my report to be able to print out either Staff, Visitor or Inmate depending on which the Person is, just not sure how to link all the data together

My SQL so far looks like this:

SELECT e.EventTypeID, EventTime, e.Description, e.PeopleID, e.EventID,(p.Forename + ' ' + p.Surname) AS UserName, (p.PTYPEID) as Operator 
FROM Events e
LEFT OUTER JOIN eventtypes t on e.EventTypeID = t.EventTypeID 
LEFT OUTER JOIN people p on p.PeopleID = e.PeopleID
WHERE (e.eventtypeid > 3000) and (e.eventtypeid < 4000)

That only prints the number, 1/2/3, how could i access the Description of the PTYPE?

Cheers
Endo

bonkey

Endurance Man wrote: »

how could i access the Description of the PTYPE?

By joining to the PType table...