Solved

Trying to SELECT two unique IDs from one table

Posted on 2004-10-01
3
191 Views
Last Modified: 2008-03-06
Cheers all,

My title isn't very descriptive of my situation, so hopefully I can do a bit better here.

First of all, here's my SQL statement:

SELECT tblShoutOut.soID, tblShoutOut.soShouter, tblShoutOut.soShoutee, tblEmployees.empFName, tblEmployees.empLName
FROM tblShoutOut, tblEmployees
WHERE tblShoutOut.soActive = 1 AND tblShoutOut.soShouter = tblEmployees.empUsername AND tblShoutOut.soShoutee = tblEmployees.empUsername

I've got 2 tables: tblShoutOut and tblEmployees.  The unique key in tblEmployees is empUsername.  In my other table, tblShoutOut, I've got several fields that I'm trying to SELECT from: tblShoutOut.soID, tblShoutOut.soShouter, tblShoutOut.soShoutee.  Also in my SELECT statement I'm grabbing two field from tblEmployees: tblEmployees.empFName, tblEmployees.empLName.

The trick is, since soShouter = empUsername AND soShoutee = (a different)empUsername, I'm not getting any results.  Is there a way to do something like this:

SELECT tblShoutOut.soID, tblShoutOut.soShouter, tblShoutOut.soShoutee, tblEmployees.empFName (for soShouter), tblEmployees.empLName (for soShouter), tblEmployees.empFName (for soShoutee), tblEmployees.empLName (for soShoutee)

Thanks for all your help!
0
Comment
Question by:saoirse1916
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
SoftEng007 earned 250 total points
ID: 12202523
SELECT tblShoutOut.soID, tblShoutOut.soShouter, tblShoutOut.soShoutee, A.empFName as ShouterFName, A.empLName as ShouterLName,B.empFName as ShouteeFName, B.empLName as ShouteeLName
FROM tblShoutOut inner join tblEmployees A
ON  tblShoutOut.soShouter = A.empUsername
inner join tblEmployees B
ON tblShoutOut.soShoutee = B.empUsername
WHERE tblShoutOut.soActive = 1
0
 
LVL 8

Author Comment

by:saoirse1916
ID: 12202826
Awesome!  Thanks very much!
0
 
LVL 8

Author Comment

by:saoirse1916
ID: 12202836
I figured it had to be one of the joins...I'd used LEFT JOIN on another part of the site...now I know what INNER JOIN does.  Thanks again!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Importing Backpac? 1 20
Help with SQL Server Polygon 2 32
Query group by data in SQL Server - cursor? 3 29
T-SQL Default value in Select? 5 22
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 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