I need some help with syntax for an SQL query.(MS SQL 2000)
There are 2 tables: PEOPLE and ACTIVITY
PEOPLE contains all the staff (one person per record)
ACTIVITY lists things they have done. There are two fields linking (matching) back to the PEOPLE.USERID field that are ACTIVITY.SALES and ACTIVITY.CLERK. Sometimes the same person can do both functions, sometimes SALES and CLERK are different PEOPLE.
I want to extract the "Full_Name" from the PEOPLE table matching the PEOPLE.USERID to either ACTIVITY.SALES or ACTIVITY.CLERK.
Here is what I have thus far:
ACTIVITY.TRANSNUMBER AS 'Trans',
PEOPLE.FULLNAME AS 'SALES',
PEOPLE.FULLNAME AS 'CLERK'
(activity.sales=people.userid or activity.clerk=people.userid)
order by activity.transnumber
I'm getting only one person and one record listed and I know that there are multiple Activities per person and Activities where SALES and CLERK are different people.