how do i get the column values?

  • I have this table called parent table.

    Id | LastName | FirstName | Gender | ParentID

    1 | Jones | Bob | M | NULL have no parent

    2 | Allen | Larry | M | NULL have no parent

    3 | Martins | Mary | F | NULL have no parent

    4 | Martins | Charles | M | 3 parent name MARY

    5 | Martins | David | M | 3 parent name MARY

    6 | Martins | Shirley | F | 3 parent name MARY

    7 | Martins | Noxy | F | 6 parent name SHIRLEY

    I need a query that can select the ID, Lastname, FirstName, Gender with parentname using the parentID

  • If its a single level then

    Select

    *

    From Parent P1

    LEFT JOIN Parent P2 on P1.Id=P2.ParentId

    If its a multi level then you will need to lookup a Recursive CTE http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Firstly, when you ask a question it is polite to provide readily consumable sample data like this: -

    --==CONDITIONALLY DROP THE SAMPLE DATA TABLE==--

    IF object_id('tempdb..#yourSampleData') IS NOT NULL

    BEGIN

    DROP TABLE #yourSampleData;

    END;

    --==FILL THE SAMPLE DATA TABLE WITH THE DATA THAT YOU HAVE SPECIFIED==--

    SELECT Id, LastName, FirstName, Gender, ParentID

    INTO #yourSampleData

    FROM (VALUES(1,'Jones','Bob','M',NULL),(2,'Allen','Larry','M',NULL),

    (3,'Martins','Mary','F',NULL),(4,'Martins','Charles','M',3),

    (5,'Martins','David','M',3),(6,'Martins','Shirley','F',3),

    (7,'Martins','Noxy','F',6)

    )a(Id, LastName, FirstName, Gender, ParentID);

    That way, anyone wanting to help you can do so with minimal effort.

    As for your particular question - I'm sure that whoever is teaching you will have talked about joins. This is an OUTER join.

    You could do it in this way: -

    SELECT main.Id, main.LastName, main.FirstName, main.Gender,

    ISNULL('parent name ' + outerA.FirstName,'have no parent')

    FROM #yourSampleData main

    OUTER APPLY (SELECT innerQ.FirstName

    FROM #yourSampleData innerQ

    WHERE innerQ.Id = main.ParentID) outerA;

    Or this: -

    SELECT main.Id, main.LastName, main.FirstName, main.Gender,

    ISNULL('parent name ' + outerA.FirstName,'have no parent')

    FROM #yourSampleData main

    LEFT OUTER JOIN #yourSampleData outerA ON outerA.Id = main.ParentID;

    Both of which return this: -

    Id LastName FirstName Gender

    ----------- -------- --------- ------ -------------------

    1 Jones Bob M have no parent

    2 Allen Larry M have no parent

    3 Martins Mary F have no parent

    4 Martins Charles M parent name Mary

    5 Martins David M parent name Mary

    6 Martins Shirley F parent name Mary

    7 Martins Noxy F parent name Shirley

    You may want to reconsider the design of your table, as each "Id" can have only one parent in your model.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thank you so much!

    Please pardon my ignorance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply