Recursive Select Statement

  • 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

  • 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

  • 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