Help needed in Getting ParentData

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 2008 Forums

Transact-SQL (2008)

Help needed in Getting ParentData

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-13 : 21:10:05

Here is my table structure

Create table ParentGroup(IdGroup int,GroupName varchar(40),GroupNumber varchar(20),
GroupCreateDt varchar(2000));

Insert into ParentGroup values(1,'Sample1','GP1000','2014-01-04'),
(2,'Sample2','GP1001','2014-10-04'),(3,'Sample3','GP1002','2014-15-04'),(4,'Sample4','GP1003','2014-02-04'),
(5,'Sample5','GP1004','2014-22-04'),(6,'Sample6','GP1005','2014-13-04');

Create table ChildGroup(IdParentGroup int, IdChildGroup int);

Insert into ChildGroup values(1,2),(1,3),(4,5),(4,6);

IdGroup	GroupName	GroupNumber	GroupCreateDt
1	Sample1	GP1000	1/4/2014
2	Sample1	GP1001	1/4/2014
3	Sample1	GP1002	1/4/2014
4	Sample4	GP1003	2/4/2014
5	Sample4	GP1004	2/4/2014
6	Sample4	GP1005	2/4/2014

Basically i am trying to get the parent data for the child other than that GroupNumber.

on my second table let us take the first row as an example.(1,2)

1 is parent and 2 is child.

1. Get the parent data first
2. Get all the parent data for the child other than GroupNumber. Because i should show the Groupnumber belongs to the child and rest belongs to the parent.

Any Samples please how to achieve this.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-13 : 21:25:21

[code]select g.*
from ParentGroup g
where exists
(
select *
from ChildGroup p
where p.IdParentGroup = g.IdGroup
)
union all
select g.IdGroup, p.GroupName, p.GroupNumber, p.GroupCreateDt
from ParentGroup g
inner join ChildGroup c on g.IdGroup = c.IdChildGroup
inner join ParentGroup p on c.IdParentGroup = p.IdGroup
order by IdGroup[/code]

KH
[spoiler]Time is always against us[/spoiler]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-13 : 21:35:23

Hi Khtan,

Thanks for you time on this. The result set which i was expecting and what you have posted mismatches.

Please take a look at the second row of your query result. The groupnumber has to be GP1001 because this is the only column i need ot get it based on the child. not on the parent. The rest of the column has to be based on the parent.

Any suggestions please

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-13 : 21:41:13

[code]select g.*
from ParentGroup g
where exists
(
select *
from ChildGroup p
where p.IdParentGroup = g.IdGroup
)
union all
select g.IdGroup, p.GroupName, g.GroupNumber, p.GroupCreateDt
from ParentGroup g
inner join ChildGroup c on g.IdGroup = c.IdChildGroup
inner join ParentGroup p on c.IdParentGroup = p.IdGroup
order by IdGroup[/code]

KH
[spoiler]Time is always against us[/spoiler]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-13 : 21:44:38

Thanks a lot. Just for my curiosity, Can the same logic be written using Outer Apply?

The reason being, i will be doing this with 200*1000 records of my table. will union all hits the performance issue?

Any suggestions please

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-13 : 21:55:14

[code]select g.IdGroup, coalesce(c.GroupName, g.GroupName) as GroupNamem, g.GroupNumber, coalesce(c.GroupCreateDt, g.GroupCreateDt) as GroupCreateDt
from ParentGroup g
outer apply
(
select p.GroupName, p.GroupCreateDt
from ChildGroup c
inner join ParentGroup p on c.IdParentGroup = p.IdGroup
where c.IdChildGroup = g.IdGroup
) c[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-13 : 21:55:41

try out and see which gives you better performance

KH
[spoiler]Time is always against us[/spoiler]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-13 : 22:08:16

Sure i will definitely try and post back. one quick scenario,

please see the below my table,

 Create table ParentGroup(IdGroup int,GroupName varchar(40),GroupNumber varchar(20),
GroupCreateDt varchar(2000));

Insert into ParentGroup values(1,'Sample1','GP1000','2014-01-04'),
(2,'Sample2','GP1001','2014-10-04'),(3,'Sample3','GP1002','2014-15-04'),(4,'Sample4','GP1003','2014-02-04'),
(5,'Sample5','GP1004','2014-22-04'),(6,'Sample6','GP1005','2014-13-04'),(7,'Sample7','GP1006','2014-13-05');

Create table ChildGroup(IdParentGroup int, IdChildGroup int);

Insert into ChildGroup values(1,2),(1,3),(4,5),(4,6);

i have added this line : (7,'Sample7','GP1006','2014-13-05')

So when i run the first query it just pulls the 6 rows. why it left the last row?

If i am not wrong the below conditions makes this issue.

where	exists
	(
		select	*
		from	ChildGroup p		
		where	p.IdParentGroup	= g.IdGroup
	)

how to override this issue. i need to bring the orphan parent record as well. Any suggestions please

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-13 : 22:24:47

Outer apply gives 7 records includes my orphaan parent which expected result. Whereas the first query didn't

Because in my 200*1000 data, 150*1000 is orphan. rest 50k has the relationship . so i couldn't use the first Query(using union all) because it just brings the relationship data and omits the orphan.

So without getting the orphan parent, i cold find the speed btw your two queries. any suggestions or corrections on the first query please

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-13 : 22:29:20

quote:
Originally posted by sqllover

Sure i will definitely try and post back. one quick scenario,

please see the below my table,
Create table ParentGroup(IdGroup int,GroupName varchar(40),GroupNumber varchar(20),
GroupCreateDt varchar(2000));

Insert into ParentGroup values(1,'Sample1','GP1000','2014-01-04'),
(2,'Sample2','GP1001','2014-10-04'),(3,'Sample3','GP1002','2014-15-04'),(4,'Sample4','GP1003','2014-02-04'),
(5,'Sample5','GP1004','2014-22-04'),(6,'Sample6','GP1005','2014-13-04'),(7,'Sample7','GP1006','2014-13-05');

Create table ChildGroup(IdParentGroup int, IdChildGroup int);

Insert into ChildGroup values(1,2),(1,3),(4,5),(4,6);

i have added this line : (7,'Sample7','GP1006','2014-13-05')

So when i run the first query it just pulls the 6 rows. why it left the last row?

If i am not wrong the below conditions makes this issue.

where exists
(
select *
from ChildGroup p
where p.IdParentGroup = g.IdGroup
)

how to override this issue. i need to bring the orphan parent record as well. Any suggestions please

the condition checks for parents only. For case when parent without any child, you can change to check for child instead

NOT EXISTS
(
    SELECT *
    FROM   ChildGroup c
    WHERE  c.IdChildGroup = g.IdGroup
)

KH
[spoiler]Time is always against us[/spoiler]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-13 : 22:35:48

Awesome. didn't strike my mind. my bad. Thank you Sir. Here is final version after seeing your tip. appreciate your time.

select	g.*
from	ParentGroup g
where not	exists
	(
		select	*
		from	ChildGroup p		
		where	p.IdChildGroup= g.IdGroup
	)
union all
select	g.IdGroup, p.GroupName, g.GroupNumber, p.GroupCreateDt
from	ParentGroup g
	inner join ChildGroup c		on	g.IdGroup	= c.IdChildGroup
	inner join ParentGroup p	on	c.IdParentGroup	= p.IdGroup
order by IdGroup

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-05-13 : 22:38:37

After my comparison with these two Union all vs Outer Apply, Union all logic won the track. very faster than outer apply.

Wonderful learning for me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-13 : 22:44:08

welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources