Need Extract Query help

Transact-SQL
1

Hi Team

I need to find out parentID having child (Child name is same and more than one childrev)

Input
ParentID ParentRev ChildName ChildRev
A 1 P 1.1
A 1 P 1.2
B 1 q 1.1
B 1 q 1.2
B 1 q 1.3
C 2 s 1.1
D 3 T 1.1
D 4 T 1.2

Output

Output
ParentID ParentRev ChildName ChildRev
A 1 P 1.1
A 1 P 1.2
B 1 q 1.1
B 1 q 1.2
B 1 q 1.3

Thanks in advance
Shreek

2

hi

i am trying to understand what the OUTPUT means ?

could you please explain ?
i am trying to make sense

once i understand what you are looking for ?
i can attempt the SQL ..

sorry

thanks
:slight_smile:
:slight_smile:

3

hi

I am Trying to get the Parent Name with Same Parent Revision , Same Child name with different Child Revisions.
In the above table for Parent - A, Parent Rev-1 has child name p with three different revisions. So I want to extract these kind of records only from the table.

For Example Parent D we have two parent revisions (3,4) and same child name (T) with different revisions. I do not need this data.

Hope u got the requirement.

Shreek

4 
Drop table if exists #Parent
create table #Parent 
(ParentID	char(1),
 ParentRev	tinyint,
 ChildName	char(1),
 ChildRev varchar(10)
)
insert into #Parent values 
('A','1','P','1.1'),
('A','1','P','1.2'),
('B','1','q','1.1'),
('B','1','q','1.2'),
('B','1','q','1.3'),
('C','2','s','1.1'),
('D','3','T','1.1'),
('D','4','T','1.2')

;with cte as 
(select ParentID, ParentRev, ChildName, count(1) n
 from #Parent
group by ParentID, ParentRev, ChildName
having count(1) > 1)

select p.*
  from #Parent p
	join cte c
		on p.ParentID = c.ParentID
		and p.ParentRev = c.ParentRev
		and p.ChildName = c.ChildName
1 Like