Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Posts
    21

    Unanswered: Getting records from one table that don't have equivalents in another table

    Hi,

    To illustrate the problem, let's say we have those two tables:

    table1
    id cost
    ===============
    1 100.15
    2 577
    3 214.975
    4 894.12

    table2
    id cost
    ===============
    1 217.13
    2 577
    3 214.975
    4 214.975

    I want to select all rows from both tables that don't have an equivalent value in the cost field .. for example, in table1 we have the value 100.15 which doesn't exist in table2 therefore we want it to show in the results, the tricky part here is that if you look at table2 you'll find that the value 214.975 exist twice while it only exists once in table1, I want it to show only once in the results.

    To make it clearer, this is an accounting application, we have two accounts that should be identical, so we want to select all amounts from both accounts that have no equivalent in the other account.



    (I'm using Sybase ASE 12.5)

    Your help is really appreciated

    Wal

  2. #2
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    Hi Wall,

    Code:
    select id, code
    from table1 
    where not exists (select 1 from table2 where cost = table1.cost)
    Would select all entries in table1 that don't exist in table2.

    You can do the same with table2, and union the results.

    Code:
    select id, code
    from table1 
    where not exists (select 1 from table2 where cost = table1.cost)
    union
    select id, code
    from table2
    where not exists (select 1 from table1 where cost = table2.cost)
    Only problem is that you do get double rows if an amount is within a table twice but not in the other table.

    Then again, if you only select the cost field, and you add a group by on the cost field you may have what you are looking for..

    Make sure to have an index on the cost field..
    Greetz,

    Bastiaan Olij

  3. #3
    Join Date
    Apr 2003
    Posts
    21
    thanks a lot for your reply, well, I might have not explained what I want well .. let's say that we have some value (in the cost field) in table1 that exist 7 times and that value exits in table2 4 times, what I want to have in the results is that values appearing 3 times

  4. #4
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    Hmm,

    That would be difficult to do in the sence that you don't know which is the first, second, third record with the same value.

    I can think of two ways that you could do this. One if you're only interested in knowing how many records, but not which ones. You simply do a select code, count(*) on each table and then join between the two and subtract the counts. Where the difference is 0 the count is equal you have the same number of records, where the count is different (take the absolute value of the count) you get how many records are missing in one table and not in the other.
    You will need to do an outer join and a union to get the result both ways for records not existing in one of the tables.

    If you want to identify the exact records that you have more in one table compared to the other, maybe the following is an idea:

    Code:
    /* make our table a little more usable.... */
    select id, code, idx = identity(15)
    into #table1a
    from table1
    order by code,id
    go
    
    select id, code, idx, min(idx) minidx
    into #table1b
    from table1a
    group by code /* yes exclude id and idx!! */
    go
    
    update #table1b
      set minidx = idx - minidx
    go
    
    /* also table2 */
    select id, code, idx = identity(15)
    into #table2a
    from table2
    order by code,id
    go
    
    select id, code, idx, min(idx) minidx
    into #table2b
    from table2a
    group by code /* yes exclude id and idx!! */
    go
    
    update #table2b
      set minidx = idx - minidx
    go
    
    /* now for our result */
    select id, code
    from table1b
    where not exists (select 1 from table2b where code = table1b.code and minidx=table1b.minidx)
    union
    select id, code
    from table2b
    where not exists (select 1 from table1b where code = table2b.code and minidx=table2b.minidx)
    This alobate piece of code does very little more then number your table in a specific order (namely making sure idx has sequential numbers for the same value of code) and by then subtracting the lowest number for a certain code you basically get a counter 0, 1, 2, 3.

    In your case, your table1 will only contain a value of 214.975 with minidx of 0, and table2 will contain a value of 214.975 with minidx of 0 and one with minidx of 1. The row with minidx of 1 will be returned by the final select.
    Greetz,

    Bastiaan Olij

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Since a cost on one table can match to any one record on the other table maybe all you need is a count. e.g.
    Code:
    create table #t1 (id int, cost decimal(18,4))
    create table #t2 (id int, cost decimal(18,4))
    
    insert into #t1 select
     1, 100.15  union all select 
     2, 577     union all select 
     3, 214.975 union all select 
     4, 894.12  union all select 
     5, 777     union all select 
     6, 777     union all select
     7, 777     union all select
     8, 777     union all select
     9, 777     union all select
    10, 777     union all select
    11, 200     union all select
    12, 777     union all select
    13, 200
    
    insert into #t2 select
     1, 217.13  union all select 
     2, 577     union all select
     3, 214.975 union all select
     4, 214.975 union all select
     5, 777     union all select 
     6, 777     union all select
     7, 777     union all select
     8, 777     union all select
     9, 200     union all select
    10, 200
    
    select view1.cost,cnt1=view1.cnt, cnt2=isnull(view2.cnt,0)
    from      
          (select cost,cnt=count(*) from #t1 group by cost) view1
      left outer join 
          (select cost,cnt=count(*) from #t2 group by cost) view2
        on view1.cost=view2.cost
    where view1.cnt!=isnull(view2.cnt,0)
    union -- left & right to simulate full outer join
    select isnull(view1.cost,view2.cost), isnull(view1.cnt,0), view2.cnt
    from       
          (select cost,cnt=count(*) from #t1 group by cost) view1
      right outer join 
          (select cost,cnt=count(*) from #t2 group by cost) view2
        on view1.cost=view2.cost
    where isnull(view1.cnt,0)!=view2.cnt
    order by 1

  6. #6
    Join Date
    Apr 2003
    Posts
    21
    Well, that was really close but it's still not what I want, by running your query it returned:

    100.1500 1 0
    214.9750 1 2
    217.1300 0 1
    777.0000 7 4
    894.1200 1 0

    the value 777 appears only once whereas it should appear 3 times (as it exists 7 times in the first table and 4 times in the second table)

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    select id,cost,'Not in table 2' 'Description'
    from (
      select a.id,a.cost,count(*) 'cq'
      from #t1 a, #t1 b
      where a.cost=b.cost
        and a.id>=b.id
      group by a.id,a.cost
      ) view1
    where not exists 
    (select * from (
        select a.id,a.cost,count(*) 'cq'
        from #t2 a, #t2 b
        where a.cost=b.cost
          and a.id>=b.id
        group by a.id,a.cost
        ) view2 
      where view1.cost=view2.cost
        and view1.cq=view2.cq
    ) 
    union all
    select id,cost,'Not in table 1' 'Description'
    from (
      select a.id,a.cost,count(*) 'cq'
      from #t2 a, #t2 b
      where a.cost=b.cost
        and a.id>=b.id
      group by a.id,a.cost
      ) view2
    where not exists 
    (select * from (
        select a.id,a.cost,count(*) 'cq'
        from #t1 a, #t1 b
        where a.cost=b.cost
          and a.id>=b.id
        group by a.id,a.cost
        ) view1
      where view2.cost=view1.cost
        and view2.cq=view1.cq
    ) 
    order by cost

Posting Permissions

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