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: Wednesday, November 12, 2014 7:59 AM
Points: 2, Visits: 37
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
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 953, Visits: 2,626
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, December 17, 2014 1:30 AM
Points: 2,386, Visits: 7,622
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
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: Wednesday, November 12, 2014 7:59 AM
Points: 2, Visits: 37
Thank you so much!
Please pardon my ignorance.
Post #1383648
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse