Solved

Trying to SELECT two unique IDs from one table

Posted on 2004-10-01
3
190 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

863 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

20 Experts available now in Live!

Get 1:1 Help Now