Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2003
    Posts
    126

    Unanswered: Table Comparison

    I have two tables that are the same. The only difference is the amount of data in one vs. the other.

    I know I have 1469186 rows in table A I also know there is 1463219 rows in table b, there is a difference of 5967 rows, and I want to see what those rows are, how can I write a query to figure it out?

    Thanks,

    Ken

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    crude but usabel...


    select a.*
    from TableA a
    left join TableB b on a.keycol = b.keycol
    where b.keycol is null

    select b.*
    from TableB b
    left join TableA a on b.keycol = a.keycol
    where a.keycol is null
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jan 2003
    Posts
    126
    Thanks for the reply!!!!


    I used:

    Code:
    select a.*
    from rg_ba_orders a
    left join rg_ba_orders1 b on a.[order number] = b.[order number]
    where b.[order number] is null
    
    select b.*
    from rg_ba_orders1 b
    left join rg_ba_orders a on b.[order number] = a.[order number]
    where a.[order number] is null
    But it returned nothing? Did I not apply something right?

    Ken

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    off hand I would say it was okay...

    Code:
    create table #rg_ba_orders([order number] int, [order desc] varchar(10))
    create table #rg_ba_orders1([order number] int, [order desc] varchar(10))
    
    
    insert into #rg_ba_orders values(1,'A')
    insert into #rg_ba_orders values(2,'B')
    
    insert into #rg_ba_orders values(3,'C')
    insert into #rg_ba_orders values(4,'D')
    insert into #rg_ba_orders1 values(3,'C')
    insert into #rg_ba_orders1 values(4,'D')
    
    insert into #rg_ba_orders1 values(5,'E')
    insert into #rg_ba_orders1 values(6,'F')
    
    
    select 'A' as 'Table',a.*
    from #rg_ba_orders a
    left join #rg_ba_orders1 b on a.[order number] = b.[order number]
    where b.[order number] is null
    union
    select 'B' as 'Table',b.*
    from #rg_ba_orders1 b
    left join #rg_ba_orders a on b.[order number] = a.[order number]
    where a.[order number] is null
    order by 1,2

    have you looked at a few rows to see if the keys columns line up?
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Jan 2003
    Posts
    126
    I think that is part of the problem.

    Is there a way to select non distinct rows? I was thinking if I could I might be able to use a union query.

    Both tables don't have a primary key.

    Ken

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    add to the prior example...

    Code:
    select 'In B Not In A' as 'Info', t1.* from (select * from #rg_ba_orders union select * from #rg_ba_orders1) t1
               left join #rg_ba_orders a on t1.[order number] = a.[order number] and t1.[order desc] = a.[order desc] 
     where a.[order number] is null
    union
    select 'In A Not In B' as 'Info', t1.* from (select * from #rg_ba_orders union select * from #rg_ba_orders1) t1
               left join #rg_ba_orders1 a on t1.[order number] = a.[order number] and t1.[order desc] = a.[order desc] 
     where a.[order number] is null
    order by 1,2
    Not the best solution but I think you get the idea
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Jan 2003
    Posts
    126
    This also works, but for some reason it doens't work on the two tables in question:

    Code:
    select * from B
          WHERE [Order Number] not
          IN (SELECT [Order Number]
                 FROM A)
    I have no idea why it will not select the extra rows. It continues to return nothing.

    I'll keep trying and post back when I find the answer.

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Post the DDL for both tables, could be something small is being over looked.
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Jan 2003
    Posts
    126
    What is DDL? and I'll post it!

  10. #10
    Join Date
    Jan 2003
    Posts
    126

    Angry DOH

    Well, I found the problem, table 1 had duplicates in it, which it should not of.

    I ran:

    Code:
    select count(distinct [order number]) from rg_ba_orders
    select count(distinct [order number]) from rg_ba_orders1
    and the counts where the same. They were duplicated and not unique rows due to a rounding error on some of the values in one of the tables.

    I learned a lot though, and there is definetly more then one way to skin a problem.

    Tell me what DDL is so I know!

    Thanks for all your help!!

    Ken

  11. #11
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    from BOL:

    The SQL language has two main divisions: Data Definition Language (DDL), which is used to define and manage all the objects in an SQL database, and Data Manipulation Language (DML), which is used to select, insert, update, and delete data in the objects defined using DDL. The Transact-SQL DDL used to manage objects such as databases, tables, and views is based on SQL-92 DDL statements, with extensions. For each object class, there are usually CREATE, ALTER, and DROP statements, such as CREATE TABLE, ALTER TABLE, and DROP TABLE. Permissions are controlled using the SQL-92 GRANT and REVOKE statements, and the Transact-SQL DENY statement.
    Paul Young
    (Knowledge is power! Get some!)

  12. #12
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: Table Comparison

    If you still want to know what exact rows are duplicated, try this:

    select A.* from A join
    (select [order number] from A group by keycol having count([order number])>1) T1
    on A.[order number]=T1.[order number] order by A.[order number]


    if the key column of you table, are in fact two or more columns:

    select A.* from A join
    (select keycol1,keycol2 from A group by keycol1,keycol2 having count(*)>1) T1
    on A.keycol1=T1.keycol1 and A.keycol2=T1.keycol2 order by A.keycol1,A.keycol2

    if the key column is of type uniqueidentifier (GUID datatype in SQLServer) you cannot use the syntax count(GUIDcol), instead use count(convert(char(38),GUIDcol)


    IONUT

Posting Permissions

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