Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81

    Unanswered: An interview question

    Hi,

    My wife had an interview today. Below is the sql-question she was asked:

    There are 2 tables, A and B. The tables have same structure - each of them consists of 100 columns named c_1 through c_100. There are no primary keys defined on the tables.
    Each table has 10,000 rows. Most rows are identical for tables A and B, however there are few that are not.
    She was required to show only those rows that have no match in the other table - by 1 query that will be short, i.e. - will not contain someting like "col_1,col_2,.._col_99,Col_100" .
    --
    kukuk

  2. #2
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76
    Hmm...interesting question. It's hard to think of how to do this if there is no field(s) that can be used to join the two tables together, but part of the way might be doing a UNION on the two tables since they have the same structure, then using the UNION as a sub-query for an outer GROUP BY and do a HAVING Count(*) = 1. That way, it'll only show records that do not have a match to another table.

    The problem with this is that you don't know which table contains that record and you do have to do the GROUP BY on all the fields outputted from the UNION query.

    I'm kinda reaching here, but maybe create some type of primary key composed of the data in each row...by creating an expression like SOUNDEX(field1 + field2 + ... field99 + field100). That way, you have a primary key that might be dependable for use in an OUTER or FULL join. Just another idea...

    Maybe something like this could be part of the way...I'm curious as to what the answer would be myself, especially if the answer has to be "1 query that will be short".

    Kael V. Dowdy MCSD, MCP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think the answer involves using the BINARY_CHECKSUM function, but I'll have to check it out.

    blindman

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, here goes:

    select *
    from (select BINARY_CHECKSUM(*) CheckSum, * from TableA) TableA
    full outer join (select BINARY_CHECKSUM(*) CheckSum, * from TableB) TableB
    on TableA.CHeckSum = TableB.Checksum
    where TableA.Checksum is null or TableB.Checksum is null

    The full outer join should show any differences between the two tables.

Posting Permissions

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