none
help in Comparing query

    Question

  •  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 'SET-A' 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))
     --SET-A
     insert into #A1 values ('A','T1')
     insert into #A1 values ('A','T2')
     insert into #A1 values ('A','T3')
     --SET-B
     insert into #A1 values ('B','T1')
     insert into #A1 values ('B','T2')
     --SET-C
     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:(SET-A has all same data (C2) which table #B1 has (C2))
     C1   C2
     ---  ---
     A    T1
     A    T2
     A    T3
    Saturday, October 19, 2013 9:34 PM

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
    Saturday, October 19, 2013 11:06 PM

All replies

  • Hi

    your query must be like this :

    Selecta.C1,a.C2from#A1a  join#B1bon

    a.C2=b.c2anda.C1='A'

    to have this result :

     C1   C2
     ---  ---
     A    T1
     A    T2
     A    T3

    Regards.

    Saturday, October 19, 2013 9:41 PM
  • Hi. The query is wrong. The query should compare all data, as per criteria. I don't want to use the following condition in the query because the data can be changed.

    anda.C1='A'

    Saturday, October 19, 2013 9:52 PM
  • Expecting result:(SET-A 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

    Saturday, October 19, 2013 10:21 PM
  • Hi, try this solution we will use a temporary table but that will give us a good result :

    Select  a.C1,a.C2into#test1from#A1a   join#B1bon  a.C2=b.c2

    Select*from#test1A   where (selectcount(C1)from#test1BwhereA.C1=B.C1)>=(selectcount(C2)from  #B1)

    Regards.

    Saturday, October 19, 2013 10:36 PM
  • 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))
     --SET-A
     insert into #A1 values ('A','T1')
     insert into #A1 values ('A','T2')
     insert into #A1 values ('A','T3')
     --SET-B
     insert into #A1 values ('B','T1')
     insert into #A1 values ('B','T2')
     --SET-C
     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 SET-B is exactly matching with #B1 (c2 field).
     
    C1        C2
    --      ---
    B       T1  
    B       T2 

    Saturday, October 19, 2013 10:51 PM
  • 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
    Saturday, October 19, 2013 11:06 PM
  • Hi, It's working now.

    Thank you so much for your help.

    Sunday, October 20, 2013 1:14 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 Morgan-Kaufmann 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

    Sunday, October 20, 2013 9:42 PM
  • 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 Morgan-Kaufmann 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

    Sunday, October 20, 2013 9:46 PM