Solved

MS SQL Query Help

Posted on 2008-10-26
3
214 Views
Last Modified: 2012-05-05
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
Comment
Question by:BJMcDonald
3 Comments
 
LVL 5

Accepted Solution

by:
Cvijo123 earned 125 total points
Comment Utility
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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
 

Author Closing Comment

by:BJMcDonald
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now