Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how do i get the column values? Expand / Collapse
Author
Message
Posted Monday, November 12, 2012 4:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 15, 2015 7:50 AM
Points: 2, Visits: 45
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
Post #1383602
Posted Monday, November 12, 2012 5:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 23, 2015 5:55 AM
Points: 1,042, Visits: 3,030
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
Post #1383621
Posted Monday, November 12, 2012 5:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 26, 2015 7:10 AM
Points: 2,468, Visits: 8,053
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

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Post #1383627
Posted Monday, November 12, 2012 6:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 15, 2015 7:50 AM
Points: 2, Visits: 45
Thank you so much!
Please pardon my ignorance.
Post #1383648
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse