How To get Child Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How To get Child Data

Author

Topic

ranganath
Posting Yak Master

209 Posts

Posted - 2009-02-16 : 08:13:21

Hi,

This is the Table Structure. and providing Sample data as follows

DECLARE @TABLE TABLE
(
TITLE_OBJECT_OBJECT_ID VARCHAR(128),
GROUP_VALUE VARCHAR(64),
GROUP_TYPE VARCHAR(64)
)
INSERT INTO @TABLE
SELECT '247B3713-5C3A-466F-B56C-05C9778A391F', 'CHILD', 'GROUP Z' UNION ALL
SELECT '247B3713-5C3A-466F-B56C-05C9778A391F', 'PARENT','GROUP Z' UNION ALL
SELECT '022B38F2-9DF7-4B9F-B8DF-177041479DC4', 'PARENT','GROUP Z' UNION ALL
SELECT '7E922E13-7EBF-4D71-8159-1F7582241FE9', 'CHILD', 'GROUP Z' UNION ALL
SELECT '7E922E13-7EBF-4D71-8159-1F7582241FE9', 'PARENT', 'GROUP Z' UNION ALL
SELECT '18B2E6BC-366C-4E95-BD65-2102397221A9', 'CHILD', 'GROUP Z' UNION ALL
SELECT '18B2E6BC-366C-4E95-BD65-2102397221A9', 'PARENT','GROUP Z' UNION ALL
SELECT '5781885B-E7B8-4C71-9A2F-324ECDEC43CF', 'PARENT','GROUP Z' UNION ALL
SELECT 'D14F93C2-A756-495D-ACC7-4CF0C9797927', 'PARENT','GROUP Z' UNION ALL
SELECT '57CF7791-E388-4A9B-B2A7-7CA06523F2EA', 'CHILD','GROUP Z'

SELECT * FROM @TABLE

i need only child Details which have no parents.

i tried with CTE But iam Not Getting the Data. please help me.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-16 : 08:17:19

Is this?

SELECT * FROM @TABLE AS T
WHERE NOT EXISTS(SELECT * FROM @TABLE WHERE TITLE_OBJECT_OBJECT_ID=T.TITLE_OBJECT_OBJECT_ID
AND GROUP_VALUE='PARENT')

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 08:56:21

[code]SELECT TITLE_OBJECT_OBJECT_ID,GROUP_TYPE
FROM @TABLE
GROUP BY TITLE_OBJECT_OBJECT_ID,GROUP_TYPE
HAVING SUM(CASE WHEN GROUP_VALUE='CHILD' THEN 1 ELSE 0 END) >0
AND SUM(CASE WHEN GROUP_VALUE='PARENT' THEN 1 ELSE 0 END) =0
[/code]

Padmaja
Starting Member

6 Posts

Posted - 2009-02-17 : 05:58:29

SELECT distinct
b.title_object_object_id,
b.group_value,
b.group_type
FROM
(select title_object_object_id,count(title_object_object_id) as cnt
from @table t group by title_object_object_id ) as a
CROSS APPLY
(select * from @table as tt where tt.title_object_object_id = a.title_object_object_id and a.cnt < 2 and group_value <> 'parent') as b

padmaja

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-17 : 06:17:15

[code]
DECLARE @TABLE TABLE
(
TITLE_OBJECT_OBJECT_ID VARCHAR(128),
GROUP_VALUE VARCHAR(64),
GROUP_TYPE VARCHAR(64)
)
INSERT INTO @TABLE
SELECT '247B3713-5C3A-466F-B56C-05C9778A391F', 'CHILD', 'GROUP Z' UNION ALL
SELECT '247B3713-5C3A-466F-B56C-05C9778A391F', 'PARENT','GROUP Z' UNION ALL
SELECT '022B38F2-9DF7-4B9F-B8DF-177041479DC4', 'PARENT','GROUP Z' UNION ALL
SELECT '7E922E13-7EBF-4D71-8159-1F7582241FE9', 'CHILD', 'GROUP Z' UNION ALL
SELECT '7E922E13-7EBF-4D71-8159-1F7582241FE9', 'PARENT', 'GROUP Z' UNION ALL
SELECT '18B2E6BC-366C-4E95-BD65-2102397221A9', 'CHILD', 'GROUP Z' UNION ALL
SELECT '18B2E6BC-366C-4E95-BD65-2102397221A9', 'PARENT','GROUP Z' UNION ALL
SELECT '5781885B-E7B8-4C71-9A2F-324ECDEC43CF', 'PARENT','GROUP Z' UNION ALL
SELECT 'D14F93C2-A756-495D-ACC7-4CF0C9797927', 'PARENT','GROUP Z' UNION ALL
SELECT '57CF7791-E388-4A9B-B2A7-7CA06523F2EA', 'CHILD','GROUP Z'

select * from
(select *,row_number() over (partition by TITLE_OBJECT_OBJECT_ID order by group_value desc) as rowno
from @table ) t
where group_value = 'child' and rowno = 1

[/code]

Jai Krishna

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-17 : 06:42:02

SELECT * FROM @TABLE where title_object_object_id not in
(select title_object_object_id from @table where group_value ='parent')

Karthik

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-17 : 06:52:10

[code]
try this one also
SELECT P.*
FROM @table p
LEFT JOIN
@table c ON p.TITLE_OBJECT_OBJECT_ID = c.TITLE_OBJECT_OBJECT_ID
AND p.GROUP_TYPE = c.GROUP_TYPE
AND c.GROUP_VALUE = 'PARENT'
WHERE
p.GROUP_VALUE = 'CHILD'
AND c.TITLE_OBJECT_OBJECT_ID IS NULL
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 08:55:32

quote:

Originally posted by Jai Krishna


DECLARE @TABLE TABLE
(
TITLE_OBJECT_OBJECT_ID VARCHAR(128),
GROUP_VALUE VARCHAR(64),
GROUP_TYPE VARCHAR(64)
)
INSERT INTO @TABLE
SELECT '247B3713-5C3A-466F-B56C-05C9778A391F', 'CHILD', 'GROUP Z' UNION ALL
SELECT '247B3713-5C3A-466F-B56C-05C9778A391F', 'PARENT','GROUP Z' UNION ALL
SELECT '022B38F2-9DF7-4B9F-B8DF-177041479DC4', 'PARENT','GROUP Z' UNION ALL
SELECT '7E922E13-7EBF-4D71-8159-1F7582241FE9', 'CHILD', 'GROUP Z' UNION ALL
SELECT '7E922E13-7EBF-4D71-8159-1F7582241FE9', 'PARENT', 'GROUP Z' UNION ALL
SELECT '18B2E6BC-366C-4E95-BD65-2102397221A9', 'CHILD', 'GROUP Z' UNION ALL
SELECT '18B2E6BC-366C-4E95-BD65-2102397221A9', 'PARENT','GROUP Z' UNION ALL
SELECT '5781885B-E7B8-4C71-9A2F-324ECDEC43CF', 'PARENT','GROUP Z' UNION ALL
SELECT 'D14F93C2-A756-495D-ACC7-4CF0C9797927', 'PARENT','GROUP Z' UNION ALL
SELECT '57CF7791-E388-4A9B-B2A7-7CA06523F2EA', 'CHILD','GROUP Z'

select * from 
(select *,row_number() over (partition by TITLE_OBJECT_OBJECT_ID order by group_value desc) as rowno
from @table ) t
where group_value = 'child' and rowno = 1

Jai Krishna

how will this ensure if it has an associated parent record or not?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 08:58:41

quote:
Originally posted by karthik_padbanaban

SELECT * FROM @TABLE where title_object_object_id not in
(select title_object_object_id from @table where group_value ='parent')

Karthik

will work for sample data given. but will not work under cases where you've other group values existi8ng

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources