• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

MS SQL Query Help

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:

  SELECT
  ACTIVITY.TRANSNUMBER AS 'Trans',
  PEOPLE.FULLNAME AS 'SALES',
  PEOPLE.FULLNAME AS 'CLERK'
  FROM ACTIVITY,PEOPLE
  WHERE
  (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.
0
BJMcDonald
Asked:
BJMcDonald
1 Solution
 
Cvijo123Commented:
try this one:
SELECT 
  ACTIVITY.TRANSNUMBER AS 'Trans',
  pplSales.FULLNAME AS 'SALES',
  pplClerk .FULLNAME AS 'CLERK'
  FROM ACTIVITY
  left join PEOPLE pplSales on ACTIVITY.sales = pplSales.UserId
  left join PEOPLE pplClerk on ACTIVITY.clerk = pplClerk .UserId
 order by activity.transnumber

Open in new window

0
 
BrandonGalderisiCommented:
No points please.  But I wanted to confirm that cvijo123's method is the correct way to achieve this.

Just one minor correction, which I'm sure was a copy/paste error, is the space removed after pplClerk and before .FULLNAME as 'CLERK'.
SELECT 
  ACTIVITY.TRANSNUMBER AS 'Trans',
  pplSales.FULLNAME AS 'SALES',
  pplClerk.FULLNAME AS 'CLERK'
  FROM ACTIVITY
  left join PEOPLE pplSales on ACTIVITY.sales = pplSales.UserId
  left join PEOPLE pplClerk on ACTIVITY.clerk = pplClerk .UserId
 order by activity.transnumber

Open in new window

0
 
BJMcDonaldAuthor Commented:
Thanks so much .... I feel pretty stupid for not considering the inner join and creating the temporary fields.  Too much coffee and not enough sleep.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now