InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 04:51:44 GMT20http://www.sqlservercentral.com/Forums/Topic1383602-1292-1.aspxThank you so much!Please pardon my ignorance.Mon, 12 Nov 2012 06:06:23 GMTaluko_niyihttp://www.sqlservercentral.com/Forums/Topic1383602-1292-1.aspxFirstly, when you ask a question it is polite to provide readily consumable sample data like this: -[code="sql"]--==CONDITIONALLY DROP THE SAMPLE DATA TABLE==--IF object_id('tempdb..#yourSampleData') IS NOT NULLBEGIN DROP TABLE #yourSampleData;END;--==FILL THE SAMPLE DATA TABLE WITH THE DATA THAT YOU HAVE SPECIFIED==--SELECT Id, LastName, FirstName, Gender, ParentIDINTO #yourSampleDataFROM (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);[/code]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: -[code="sql"]SELECT main.Id, main.LastName, main.FirstName, main.Gender,ISNULL('parent name ' + outerA.FirstName,'have no parent')FROM #yourSampleData mainOUTER APPLY (SELECT innerQ.FirstName FROM #yourSampleData innerQ WHERE innerQ.Id = main.ParentID) outerA;[/code]Or this: -[code="sql"]SELECT main.Id, main.LastName, main.FirstName, main.Gender,ISNULL('parent name ' + outerA.FirstName,'have no parent')FROM #yourSampleData mainLEFT OUTER JOIN #yourSampleData outerA ON outerA.Id = main.ParentID;[/code]Both of which return this: -[code="plain"]Id LastName FirstName Gender ----------- -------- --------- ------ -------------------1 Jones Bob M have no parent2 Allen Larry M have no parent3 Martins Mary F have no parent4 Martins Charles M parent name Mary5 Martins David M parent name Mary6 Martins Shirley F parent name Mary7 Martins Noxy F parent name Shirley[/code]You may want to reconsider the design of your table, as each "Id" can have only one parent in your model.Mon, 12 Nov 2012 05:21:18 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1383602-1292-1.aspxIf its a single level then [code="sql"]Select *From Parent P1LEFT JOIN Parent P2 on P1.Id=P2.ParentId[/code]If its a multi level then you will need to lookup a Recursive CTE [url]http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx[/url]Mon, 12 Nov 2012 05:13:22 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1383602-1292-1.aspxI have this table called parent table.Id | LastName | FirstName | Gender | ParentID1 | Jones | Bob | M | NULL have no parent2 | Allen | Larry | M | NULL have no parent3 | Martins | Mary | F | NULL have no parent4 | Martins | Charles | M | 3 parent name MARY5 | Martins | David | M | 3 parent name MARY6 | Martins | Shirley | F | 3 parent name MARY7 | Martins | Noxy | F | 6 parent name SHIRLEYI need a query that can select the ID, Lastname, FirstName, Gender with parentname using the parentIDMon, 12 Nov 2012 04:21:12 GMTaluko_niyi