November 12, 2012 at 4:21 am
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
November 12, 2012 at 5:13 am
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
November 12, 2012 at 5:21 am
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.
November 12, 2012 at 6:06 am
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