November 19, 2007 at 10:21 am
I have the following query:
Select c.[ConsultantID]
,c.[FirstName]
,c.[LastName]
,c.[SponsorID]
from Consultant C, Consultant D
Where C.SponsorID = D.ConsultantID
AND d.SponsorID Not In ('999999999-6','999999999')
ORDER BY c.ConsultantID
The key columns are ConsultantID and SponsorID which is kept for each record.
What I need to do is get the name of the sponsor based on the sponsorID which in fact is a consultantid in the table.
Any ideas or am I approaching it at the wrong angle.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
November 19, 2007 at 2:58 pm
You could probably do a sub query to reference back to the main table like such:
Select c.[ConsultantID]
,c.[FirstName]
,c.[LastName]
,c.[SponsorID]
,(SELECT [FirstName] + ' ' + [LastName] FROM Consultant WHERE ConsultantID = c.SponsorID)
from Consultant C, Consultant D
Where C.SponsorID = D.ConsultantID
AND d.SponsorID Not In ('999999999-6','999999999')
ORDER BY c.ConsultantID
November 19, 2007 at 3:07 pm
The method you are using is fine. You just have one small piece of the puzzle missing.
Select c.[ConsultantID]
,c.[FirstName]
,c.[LastName]
,c.[SponsorID]
,d.[FirstName] AS Sponser_FirstName
,d.[LastName] AS Sponser_LastName
from Consultant C, Consultant D
Where C.SponsorID = D.ConsultantID
AND d.SponsorID Not In ('999999999-6','999999999')
ORDER BY c.ConsultantID
On a different note you might find it easier if you aliased your second version of Consultant as S for Sponser.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply