Answered by:
help in Comparing query

Hi, My query's output is wrong and not getting the expecting output. I am doing query from #A1 to match data with #B1 table. Result of 'SETA' of #A1 table is completely matching with table #B1 and it should be output.
Matching field is 'C2' which is common in #A1 and #B1 table.
create table #A1 (C1 char(4),C2 char(4))
SETA
insert into #A1 values ('A','T1')
insert into #A1 values ('A','T2')
insert into #A1 values ('A','T3')
SETB
insert into #A1 values ('B','T1')
insert into #A1 values ('B','T2')
SETC
insert into #A1 values ('C','T1')
insert into #A1 values ('C','T2')
insert into #A1 values ('C','T4')
This data should be with #A1 table.
create table #B1 (C2 char(4))
insert into #B1 values ('T1')
insert into #B1 values ('T2')
insert into #B1 values ('T3')
Select a.C1,a.C2 from #A1 a join #B1 b on
a.C2=b.c2
Expecting result:(SETA has all same data (C2) which table #B1 has (C2))
C1 C2
 
A T1
A T2
A T3
Question
Answers

Hi,
try this hope that help you :
Select a.C1,a.C2 into #test1 from #A1 a join #B1 b on a.C2=b.c2 Select* from #test1 A
where(select count(C1)from #test1 B where A.C1=B.C1)=(select count(C2) from #B1) and (select count(C1)from #test1 B where A.C1=B.C1)=(select count(C2) from #A1 where A.C1=#A1.C1)
 Marked as answer by Kevin Ruth Sunday, October 20, 2013 1:13 AM
All replies



Expecting result:(SETA has all same data (C2) which table #B1 has (C2))
C1 C2
 
A T1
A T2
A T3
In your data T1 and T2 are common for SET A, B & C and so the join on C2 column of #A1 and #B1 without any other criteria would return T1 and T2 records from SET B & C apart from the whole SET A records. If you want only Set A then have a where clause for C1 = 'A'.
sIbu


Hi, I just changed the data in table #A to check the query result but not getting the desired result.
create table #A1 (C1 char(4),C2 char(4))
SETA
insert into #A1 values ('A','T1')
insert into #A1 values ('A','T2')
insert into #A1 values ('A','T3')
SETB
insert into #A1 values ('B','T1')
insert into #A1 values ('B','T2')
SETC
insert into #A1 values ('C','T1')
insert into #A1 values ('C','T2')
insert into #A1 values ('C','T4')
This data should be with #A1 table.
create table #B1 (C2 char(4))
insert into #B1 values ('T1')
insert into #B1 values ('T2')
Select a.C1,a.C2 into #test1 from #A1 a
join #B1 b on a.C2=b.c2
Select* from #test1 A where (select count(C1)from #test1 B where A.C1=B.C1)>=(select count(C2)
from #B1)
Result from the above query
C1 C2
 
A T1
A T2
B T1
B T2
C T1
C T2
Desired result (Data of #a1(C2 field) of SETB is exactly matching with #B1 (c2 field).
C1 C2
 
B T1
B T2 
Hi,
try this hope that help you :
Select a.C1,a.C2 into #test1 from #A1 a join #B1 b on a.C2=b.c2 Select* from #test1 A
where(select count(C1)from #test1 B where A.C1=B.C1)=(select count(C2) from #B1) and (select count(C1)from #test1 B where A.C1=B.C1)=(select count(C2) from #A1 where A.C1=#A1.C1)
 Marked as answer by Kevin Ruth Sunday, October 20, 2013 1:13 AM


Please post correct DDL. Tables have keys and it looks like you do not want NULLs. You also do not know that columns are not fields; this is fundamental. Let's clean it up:
CREATE TABLE Alpha – dividend
(group_name CHAR(4) NOT NULL,
t_val CHAR(4) NOT NULL,
PRIMARY KEY (group_name, t_val))
Here is the current syntax for INSERT INTO and it will save you lots of typing.
INSERT INTO Alpha
VALUES ('A', 'T1'), ('A', 'T2'), ('A', 'T3'),
('B', 'T1'), ('B', 'T2'),
('C', 'T1'), ('C', 'T2'), ('C', 'T4');
CREATE TABLE Beta  divisor
(t_val CHAR(4) NOT NULL PRIMARY KEY);
INSERT INTO Beta VALUES ('T1'), ('T2'),('T3');
What you want is called an Exact division. The dividend table must match exactly to the values of the divisor without any extra values.
SELECT A.group_name
FROM Alpha AS A
LEFT OUTER JOIN
Beta AS B
ON A.t_value = B.t_value
GROUP BY A.group_name
HAVING COUNT(A.t_value) = (SELECT COUNT(t_value) FROM Beta)
AND COUNT(B.t_value) = (SELECT COUNT(t_value) FROM Beta);
Think of an Australian aborigine who physically matches his children to arrows. He has no concept of numbers! If he has more children than arrows or more arrows than children, then something is left over after matching. If they are the same, then my matchings are equal. Read a book on basic Set Theory and look for G. Cantor's proofs.
I have a section in SQL FOR SMARTIES on this with more tricks and explanations.CELKO Books in Celko Series for MorganKaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Off topic:
Your dice are crooked! The purple die is called a "top" and cannot make certain totals. Opposite faces on a die total to 7 (1+6, 2+5, 3+4) in Western and Asia dice.
CELKO Books in Celko Series for MorganKaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL