Solved

MS SQL Query Help

Posted on 2008-10-26
3
215 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
ID: 22808313
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
ID: 22808352
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
ID: 31510134
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Query 4 45
How to find the last word in a string in SQLITE 14 69
Remove Time Stamp from a Date field 4 41
Unable to save view in SSMS 21 53
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

947 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

18 Experts available now in Live!

Get 1:1 Help Now