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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy