April 13, 2009 at 12:42 am
Hi,
I have a parent child relationship in a query. If a parent has more than one child then both has to be displayed in seperate rows.
For eg: if a parent 'A' has two childs A1,A2 then the ouput should be displayed as,
Row1 A Columns A1 Columns
Row2 A Columns A2 Columns
At present i have query which appears like row 1. So my query will satisfy the condition of having one parent having one child. If i have more than one child the same pattern of row should appear for child 2.
Any help on this would be appreciated.
April 13, 2009 at 3:21 am
Please Provide us some sample data, ur existing query and what output u getting and and whats ur desired output..
pls
Mithun
April 13, 2009 at 7:06 am
That sure sounds like a pretty standard INNER JOIN.
Try this:
CREATE TABLE #Parent
(ParentId INT IDENTITY(1,1) NOT NULL
,Val NVARCHAR(50) NOT NULL
,CONSTRAINT pkParent PRIMARY KEY CLUSTERED (ParentId))
CREATE TABLE #Child
(ChildId INT IDENTITY(1,1) NOT NULL
,ChildVal NVARCHAR(50) NOT NULL
,ParentId INT NOT NULL
,CONSTRAINT pkChild PRIMARY KEY CLUSTERED (ChildId))
INSERT INTO #Parent (
Val
) SELECT 'Row1'
UNION
SELECT 'Row2'
INSERT INTO #Child (
ChildVal,
ParentId
) SELECT
'Child of Row1 a'
,1
UNION
SELECT
'Child of Row1 b'
,1
UNION
SELECT
'Child of Row2'
,2
SELECT p.ParentId,p.Val,c.ChildVal
FROM #Parent AS p
JOIN #Child AS c
ON p.ParentId = c.ParentId
WHERE p.ParentId = 1
DROP TABLE #Parent
DROP TABLE #Child
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 13, 2009 at 8:55 am
Hi
As Grant Fritchey said, it is INNER join. But can you please provide the table structure..so that we can guide in right direction from Table Design prespective.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply