April 9, 2008 at 6:05 am
Hi all,
My first post, so may I say brilliant site. A lot of well organised and useful information.
I am in need of some help and am currently using SQL Server 2005. I've attached some SQL that will create a temporary table with sample data. The temporary table holds Parent/Child information.
I am looking to return another ParentNo/ParentRev that has the same children as, in the attached case, 000006/0. The SQL I have so far will return parents that have any child belonging to 000006/0 but I need help now getting the parents that have the exact same children as 000006/0. In the sample data, I am only expecting 000007/0 to be returned.
Any help/pointers would be greatly appreciated.
Thanks,
Colm
April 9, 2008 at 6:56 am
Like this?
--Create temporary table that holds Parent and Children records.
IF OBJECT_ID (N'tempdb..#Children', N'U') IS NOT NULL
DROP TABLE #Children;
CREATE TABLE #Children (ParentNo VarChar(50), ParentRev Int, ChildNo VarChar(50), ChildRev Int)
--Insert test data into the table
INSERT INTO #Children
SELECT '000001', 0, '000002', 0 UNION ALL
SELECT '000001', 0, '000006', 0 UNION ALL
SELECT '000002', 0, '000003', 0 UNION ALL
SELECT '000002', 0, '000004', 0 UNION ALL
SELECT '000002', 0, '000005', 0 UNION ALL
SELECT '000003', 0, '000004', 0 UNION ALL
SELECT '000006', 0, '000002', 0 UNION ALL
SELECT '000006', 0, '000004', 0 UNION ALL
SELECT '000007', 0, '000002', 0 UNION ALL
SELECT '000007', 0, '000004', 0
DECLARE @ParentNo VarChar(50),
@ParentRev Int
SET @ParentNo = '000006'
SET @ParentRev = 0
--Return Parents that have identical children to @ParentNo, @ParentRev
; with x as (select ChildNo from #Children where ParentNo = @ParentNo and ParentRev = @ParentRev)
select ParentNo
from #Children a
where ChildNo in (select ChildNo from x) and not ParentNo = @ParentNo
group by ParentNo
having count(*) = (select count(*) from x)
IF OBJECT_ID (N'tempdb..#Children', N'U') IS NOT NULL
DROP TABLE #Children;
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 9, 2008 at 7:05 am
This is basically the same solution Ryan posted without a CTE
--Create temporary table that holds Parent and Children records.
IF OBJECT_ID (N'tempdb..#Children', N'U') IS NOT NULL
DROP TABLE #Children;
CREATE TABLE #Children
(
ParentNo VarChar(50),
ParentRev Int,
ChildNo VarChar(50),
ChildRev Int
)
--Insert test data into the table
INSERT INTO #Children
SELECT '000001', 0, '000002', 0 UNION ALL
SELECT '000001', 0, '000006', 0 UNION ALL
SELECT '000002', 0, '000003', 0 UNION ALL
SELECT '000002', 0, '000004', 0 UNION ALL
SELECT '000002', 0, '000005', 0 UNION ALL
SELECT '000003', 0, '000004', 0 UNION ALL
SELECT '000006', 0, '000002', 0 UNION ALL
SELECT '000006', 0, '000004', 0 UNION ALL
SELECT '000007', 0, '000002', 0 UNION ALL
SELECT '000007', 0, '000004', 0
DECLARE @ParentNo VarChar(50),
@ParentRev Int
SET @ParentNo = '000006'
SET @ParentRev = 0
Select
C1.ParentNo,
C1.ParentRev
From
#Children C1 Join
#Children C2 On
C1.ChildNo = C2.ChildNo And
C1.ChildRev = C2.ChildRev
Where
C2.ParentNo = @ParentNo AND
C2.ParentRev = @ParentRev And
C1.ParentNo <> C2.ParentNo
Group By
C1.ParentNo,
C1.ParentRev
Having
Count(C2.ParentNo) = (Select Count(*) From #Children Where ParentNo = @ParentNo AND ParentRev = @ParentRev)
IF OBJECT_ID (N'tempdb..#Children', N'U') IS NOT NULL
DROP TABLE #Children;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 9, 2008 at 7:08 am
After running both it looks like Ryan's solution is slightly more efficient so will scale a little better.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 9, 2008 at 7:31 am
Thanks guys,
That is pretty much it. I was looking at Ryan's solution and changed the CTE slightly to be more like Jacks to take into consideration the ChildRev.
--Create temporary table that holds Parent and Children records.
IF OBJECT_ID (N'tempdb..#Children', N'U') IS NOT NULL
DROP TABLE #Children;
CREATE TABLE #Children (ParentNo VarChar(50), ParentRev Int, ChildNo VarChar(50), ChildRev Int)
--Insert test data into the table
INSERT INTO #Children
SELECT '000001', 0, '000002', 0 UNION ALL
SELECT '000001', 0, '000006', 0 UNION ALL
SELECT '000002', 0, '000003', 0 UNION ALL
SELECT '000002', 0, '000004', 0 UNION ALL
SELECT '000002', 0, '000005', 0 UNION ALL
SELECT '000003', 0, '000004', 0 UNION ALL
SELECT '000006', 0, '000002', 0 UNION ALL
SELECT '000006', 0, '000004', 0 UNION ALL
SELECT '000007', 0, '000002', 0 UNION ALL
SELECT '000007', 0, '000004', 0
DECLARE @ParentNo VarChar(50),
@ParentRev Int
SET @ParentNo = '000006'
SET @ParentRev = 0;
--Return Parents that have identical children to @ParentNo, @ParentRev
WITH x AS (
SELECT ChildNo, ChildRev
FROM #Children
WHERE ParentNo = @ParentNo
AND ParentRev = @ParentRev)
SELECT C.ParentNo, C.ParentRev
FROM #Children C, x
WHERE (C.ChildNo = x.ChildNo
AND C.ChildRev = x.ChildRev)
AND NOT C.ParentNo = @ParentNo
GROUP BY C.ParentNo, C.ParentRev
HAVING COUNT(*) = (SELECT COUNT(*) FROM x)
IF OBJECT_ID (N'tempdb..#Children', N'U') IS NOT NULL
DROP TABLE #Children;
Thanks again for your quick replies,
Colm
April 9, 2008 at 7:40 am
Are (ParentNo, ParentRev) forming a composite key? If not, ignore this post.
But if they are a composite key, the CTE solution won't work since it ignores ParentRev/ChildRev. Add this to the sample data to check:
insert into #Children values ('A', 0, 'C', 0 )
insert into #Children values ('A', 0, 'C', 1 )
insert into #Children values ('B', 0, 'C', 1 )
insert into #Children values ('B', 0, 'C', 2 )
insert into #Children values ('B', 1, 'C', 0 )
insert into #Children values ('B', 1, 'C', 1 )
insert into #Children values ('B', 2, 'C', 1 )
insert into #Children values ('B', 2, 'C', 2 )
SET @ParentNo = 'A'
SET @ParentRev = 0
The second alternative worked fine with 'A',0 but I'm suspicious of the WHERE clause since it ignores ParentRev:
[font="Courier New"]Where
C2.ParentNo = @ParentNo AND
C2.ParentRev = @ParentRev And
C1.ParentNo <> C2.ParentNo[/font]
Using @ParentNo = 'B' and @ParentRev = 0, the second alternative returns an empty set. The code below works for the additional test cases (it returns more than was asked for just for my sanity):
with Parents( ParentNo, ParentRev, kids )
as ( select ParentNo, ParentRev, count(*) as kids
from #Children
group by ParentNo, ParentRev )
select P.ParentNo, P.ParentRev, P.kids,
C2.ParentNo as matchParentNo, C2.ParentRev as matchParentRev
from Parents P
join #Children C1
on P.ParentNo = C1.ParentNo and P.ParentRev = C1.ParentRev
join #Children C2
on C1.ChildNo = C2.ChildNo and C1.ChildRev = C2.ChildRev
where P.ParentNo = 'B' and P.ParentRev = 2
group by P.ParentNo, P.ParentRev, P.kids, C2.ParentNo, C2.ParentRev
having count(*) = P.kids
April 9, 2008 at 7:51 am
Antonio,
Based on the supplied test data and the stated desired outcome if you added C1.ParentRev <> C2.ParentRev then you would not get any results because ParentRev is 0 in each record. Also the requirement was to return every ParentNo and ParentRev combination that matched exactly the children records for the Row returned based on the parameters.
ALthough you do raise an interesting point with your test data you would get no rows even though it shoud retrun an earlier rev.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 9, 2008 at 8:18 am
Hi guys,
Antonio is indeed correct in his assumption. Although I didn't specify it No/Rev is a composite key for both Parents and Children. Jack if you include the ParentRev check in the where clause in brackets, i.e. (C1.ParentNo <> C2.ParentNo AND C1.ParentRev <> C2.ParentRev), you will overcome that problem.
There is another problem that I have found, I removed a child from A/0 and all the methods return B/1 as a match - but this is not the case because A/0 has only one child but B/1 has two children. The reason is somewhere in the join but I haven't worked out how to include all the B/1 children in the count.
Any ideas for this one?
Thanks,
Colm
--Create temporary table that holds Parent and Children records.
IF OBJECT_ID (N'tempdb..#Children', N'U') IS NOT NULL
DROP TABLE #Children;
CREATE TABLE #Children (ParentNo VarChar(50), ParentRev Int, ChildNo VarChar(50), ChildRev Int)
--Insert test data into the table
INSERT INTO #Children
SELECT '000001', 0, '000002', 0 UNION ALL
SELECT '000001', 0, '000006', 0 UNION ALL
SELECT '000002', 0, '000003', 0 UNION ALL
SELECT '000002', 0, '000004', 0 UNION ALL
SELECT '000002', 0, '000005', 0 UNION ALL
SELECT '000003', 0, '000004', 0 UNION ALL
SELECT '000006', 0, '000002', 0 UNION ALL
SELECT '000007', 0, '000002', 0 UNION ALL
SELECT '000007', 0, '000004', 0
insert into #Children values ('A', 0, 'C', 0 )
--insert into #Children values ('A', 0, 'C', 1 )
insert into #Children values ('B', 0, 'C', 1 )
insert into #Children values ('B', 0, 'C', 2 )
insert into #Children values ('B', 1, 'C', 0 )
insert into #Children values ('B', 1, 'C', 1 )
insert into #Children values ('B', 2, 'C', 1 )
insert into #Children values ('B', 2, 'C', 2 )
DECLARE @ParentNo VarChar(50),
@ParentRev Int
SET @ParentNo = 'A'
SET @ParentRev = 0;
--Return Parents that have identical children to @ParentNo, @ParentRev
WITH x AS (
SELECT ChildNo, ChildRev
FROM #Children
WHERE ParentNo = @ParentNo
AND ParentRev = @ParentRev)
SELECT C.ParentNo, C.ParentRev
FROM #Children C
INNER JOIN x
ON C.ChildNo = x.ChildNo
AND C.ChildRev = x.ChildRev
WHERE NOT (C.ParentNo = @ParentNo
AND C.ParentRev = @ParentRev)
GROUP BY C.ParentNo, C.ParentRev
HAVING COUNT(*) = (SELECT COUNT(*) FROM x)
IF OBJECT_ID (N'tempdb..#Children', N'U') IS NOT NULL
DROP TABLE #Children;
April 9, 2008 at 8:48 am
Hi guys,
Here is my attempt at fixing the problem I found. It's only a snippet of the code in an attempt to keep the post tidy.
Please feel free to let me know if you have a better way.
Thanks for all your help,
Colm
--Return Parents that have identical children to @ParentNo, @ParentRev
WITH x AS (
SELECT ChildNo, ChildRev
FROM #Children
WHERE ParentNo = @ParentNo
AND ParentRev = @ParentRev)
SELECT P.*
FROM #Children C1, (SELECT C.ParentNo, C.ParentRev
FROM #Children C
INNER JOIN x
ON C.ChildNo = x.ChildNo
AND C.ChildRev = x.ChildRev
WHERE NOT (C.ParentNo = @ParentNo
AND C.ParentRev = @ParentRev)
GROUP BY C.ParentNo, C.ParentRev
HAVING COUNT(*) = (SELECT COUNT(*) FROM x)) P
WHERE C1.ParentNo = P.ParentNo
AND C1.ParentRev = P.ParentRev
GROUP BY P.ParentNo, P.ParentRev
HAVING COUNT(*) = (SELECT COUNT(*) FROM x)
IF OBJECT_ID (N'tempdb..#Children', N'U') IS NOT NULL
DROP TABLE #Children;
April 9, 2008 at 9:05 am
When comparing composite keys for row exclusion, you can't simply apply <> to all key members. Consider this simple example of a table with fields f1 and f2, and three rows:
f1,f2
----
A,1
A,2
B,1
If you attempt to exclude (A,2) via [font="Courier New"]f1 <> A and f2 <> 2[/font], only (B,1) will remain. Likewise, excluding (B,1) with [font="Courier New"]f1 <> B and f2 <> 1[/font] will only result in (A,2). So, the comparison below is not a solution:
[font="Courier New"](C1.ParentNo <> C2.ParentNo AND C1.ParentRev <> C2.ParentRev)[/font]
Here's another alternative that handles your new test condition.
select X.*, C.ChildNo, C.ChildRev
into #ParentChild
from (select ParentNo, ParentRev, count(*) as kids from #Children
group by ParentNo, ParentRev) as X
join #Children C on C.ParentNo = X.ParentNo and C.ParentRev = X.ParentRev
select P.ParentNo, P.ParentRev, P.kids, C.ParentNo as matchParentNo, C.ParentRev as matchParentRev
from #ParentChild P
join #ParentChild C
on P.ChildNo = C.ChildNo
and P.ChildRev = C.ChildRev
where P.ParentNo = '@ParentNo and P.ParentRev = @ParentRev
group by P.ParentNo, P.ParentRev, P.kids, C.ParentNo, C.ParentRev
having count(*) = P.kids and P.kids = min(C.kids)
-- and not ( C.ParentNo = P.ParentNo and C.ParentRev = P.ParentRev )
-- CTE version
DECLARE @ParentNo VarChar(50),
@ParentRev Int
SET @ParentNo = 'B'
SET @ParentRev = 0;
with ParentChild( ParentNo, ParentRev, kids, ChildNo, ChildRev )
as (
select X.ParentNo, X.ParentRev, X.kids, C.ChildNo, C.ChildRev
from (select ParentNo, ParentRev, count(*) as kids from #Children
group by ParentNo, ParentRev) as X
join #Children C on C.ParentNo = X.ParentNo and C.ParentRev = X.ParentRev )
select P.ParentNo, P.ParentRev, P.kids, C.ParentNo as matchParentNo, C.ParentRev as matchParentRev
from ParentChild P
join ParentChild C
on P.ChildNo = C.ChildNo
and P.ChildRev = C.ChildRev
where P.ParentNo = @ParentNo and P.ParentRev = @ParentRev
group by P.ParentNo, P.ParentRev, P.kids, C.ParentNo, C.ParentRev
having count(*) = P.kids and P.kids = min(C.kids)
-- and not ( C.ParentNo = P.ParentNo and C.ParentRev = P.ParentRev )
editted to include CTE version
April 9, 2008 at 9:17 am
Hi all,
I've just caught up with the thread.
This would be my latest try based on the discussions...
; with x as (select ChildNo, ChildRev from #Children where ParentNo = @ParentNo and ParentRev = @ParentRev)
select ParentNo, ParentRev
from #Children a left join x b on a.ChildNo = b.ChildNo and a.ChildRev = b.ChildRev
where not (ParentNo = @ParentNo and ParentRev = @ParentRev)
group by ParentNo, ParentRev
having count(*) = (select count(*) from x) and count(*) = count(b.ChildNo)
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 9, 2008 at 9:28 am
Thanks Antonio. You are 100% correct...what I meant to say was:
WHERE NOT (C1.ParentNo = C2.ParentNo AND C1.ParentRev = C2.ParentRev)
I had noticed that earlier but forgot to say. Sorry.
Thanks,
Colm
April 9, 2008 at 9:44 am
Thanks Ryan.
A nice concise solution.
Thanks to all for your help.
Good luck,
Colm
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply