Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    12

    Unanswered: Where Exists - produces different values depending on the table used in the subquery

    i am going really mad about this and need help!!

    I need to count how many lines are equal in two tables (table A and B) even the duplicates, basing on the fields date, area_code and num_phone.

    The two tables can have one or more records with the same date, area_code and phone_number, so there will be a lot of duplicated lines.

    The problem is: when I use: select count(*) from table A where exists in Table B, it produces me a value.

    When I use the inverse: select count(*) from table B where exists in table A, it produces another value.

    The result must be the same for both queries, cuz I am checking what is common between those tables.

    here are my queries:
    SELECT count(*), date FROM T_SIC_BILLING_ASSIST AS A WHERE EXISTS (
    SELECT * FROM T_SIC_BILLING_ANALITICO AS B WHERE a.date = b.date
    AND a.area_code = b.area_code AND a.phone_number = b.phone_number)
    AND a.date = '2007-07-04' GROUP BY a.date

    The result is: 5692 records.

    if I use the tables in the inverse order, I have:
    SELECT count(*), date FROM T_SIC_BILLING_analitico AS A WHERE EXISTS (
    SELECT * FROM T_SIC_BILLING_assist AS B WHERE a.date = b.date AND a.area_code = b.area_code AND a.phone_number = b.phone_number) AND a.date = '2007-07-04' GROUP BY a.date

    The result is: 5805 records.

    The tables structure
    T_SIC_BILLING_assist
    date - areacode - phonenumber
    2007-07-04 011 33556232
    2007-07-04 011 33526232

    2007-07-04 011 33526232
    2007-07-04 011 44551221

    T_SIC_BILLING_analitico
    2007-07-04 011 33556232
    2007-07-04 011 33556232

    2007-07-04 011 77884452

    So, I have two lines in common between the tables (the lines i marked in bold, for example), is that what I need.

    Someone can help me? I am getting desperate...

    Thanks a lot,
    venus
    Last edited by venusgirl; 10-11-07 at 15:39.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Yes, the record counts can be different e.g.
    table1:
    a
    a
    b
    c

    table2:
    a
    c
    c
    c
    d

    Now you can see 3 rows of table1 exist in table2 (a,a,c)
    and 4 rows of table 2 exists in table1 (a,c,c,c)

  3. #3
    Join Date
    Oct 2004
    Posts
    12
    Thank you for your response

    But how can I proceed if I dont know what records are in the table, the only thing i can have is the date.
    And I have about 8000 different area code + phone numbers.


    Thanks once again,
    venus

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I do not fully understand what it is you are trying to do
    What result do you expect from this data assuming it resembles what you have
    Code:
    -- Create test data
    create table #t1 (
    callid int, calldt datetime, areacd int, phoneno int ) insert into #t1 select
    1         , '20070704'     , 011       , 123           union all select
    2         , '20070704'     , 011       , 123           union all select
    3         , '20070704'     , 011       , 456           union all select
    4         , '20070704'     , 011       , 789           union all select
    5         , '20070704'     , 011       , 654           union all select
    6         , '20070704'     , 011       , 654           union all select
    7         , '20070704'     , 011       , 654
    
    create table #t2 (
    callid int, calldt datetime, areacd int, phoneno int ) insert into #t2 select
    1         , '20070704'     , 011       , 987           union all select
    2         , '20070704'     , 011       , 654           union all select
    3         , '20070704'     , 011       , 654           union all select
    4         , '20070704'     , 011       , 123           union all select
    5         , '20070704'     , 011       , 123           union all select
    6         , '20070704'     , 011       , 789           union all select
    7         , '20070704'     , 011       , 789           union all select
    8         , '20070704'     , 011       , 789           union all select
    9         , '20070704'     , 011       , 789
    
    insert into #t1 select callid+10,dateadd(dd,1,calldt), areacd, phoneno 
    from #t2 -- data for next day
    insert into #t2 select callid+10,dateadd(dd,1,calldt), areacd, phoneno 
    from #t1 where callid<10 --data for next day
    -- End create test data
    
    select a.calldt, a.areacd, a.phoneno, a.cnt table1cnt, b.cnt table2cnt 
    from 
    (select calldt, areacd, phoneno, count(*) cnt from #t1 
     group by calldt, areacd, phoneno) a, 
    (select calldt, areacd, phoneno, count(*) cnt from #t2 
     group by calldt, areacd, phoneno) b 
    where a.calldt=b.calldt
      and a.areacd=b.areacd
      and a.phoneno=b.phoneno
    
    calldt              areacd      phoneno     table1cnt   table2cnt   
    ------              ----------- ----------- ----------- ----------- 
    Jul  4 2007 12:00AM          11         123           2           2 
    Jul  4 2007 12:00AM          11         654           3           2 
    Jul  4 2007 12:00AM          11         789           1           4 
    Jul  5 2007 12:00AM          11         123           2           2 
    Jul  5 2007 12:00AM          11         654           2           3 
    Jul  5 2007 12:00AM          11         789           4           1
    
    drop table #t1
    drop table #t2

  5. #5
    Join Date
    Oct 2004
    Posts
    12
    Bingo!!
    That's what I really needed.
    Thank you soooo much!!!

    xoxo,
    venus

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •