dcsimg
Results 1 to 5 of 5
  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,750

    Resolved [RESOLVED] A query to compare two almost identical tables?

    As long as I've been doing SQL queries, I'm having a "brain fart" on this one...or maybe just getting old. I've been asked this question to solve.

    I have 2 tables, each with two columns in them. However, the latter table has 5 additional records. For simplicity, the column names are Col1 and Col2 for each table.

    TASK:
    "Write a query that tests if the records that exist in both versions of the tables are different between the two versions. Does your query let you know which field changed? How many records changed?"

    I've attached the 2 tables in a spreadsheet.

    Thanks,
    Blake

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    98,907

    Re: A query to compare two almost identical tables?

    That task seems to be rather poorly written. This:
    Write a query that tests if the records that exist in both versions of the tables are different between the two versions.
    if taken literally, suggests that the query should return a single Boolean value. I'm not sure whether the subsequent questions are supposed to provide hints as to how the query should be written or are genuine enquiries as to how your query works. On the face of it, I'd just do something like this:
    sql Code:
    1. SELECT *
    2. FROM Table1 t1 INNER JOIN Table2 t2
    3. ON t1.Id = t2.Id
    4. WHERE t1.Column1 <> t2.Column1
    5. OR t1.Column2 <> t2.Column2
    That would give you a list of all records where there was a difference and you could then visually inspect them to see where the differences were. If the actual requirement was something more specific then a more complex query would be possible but it's not clear exactly what is expected in this case.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,275

    Re: A query to compare two almost identical tables?

    Maybe
    Code:
    select t1 from Table1
    EXCEPT
    select t2 from table2
    ?
    Slow as hell.

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,772

    Re: A query to compare two almost identical tables?

    If you open the spreadsheet you'll realize the question doesn't make sense at all. There are only two columns, not a key and two columns. Either of the columns could be a key, or neither, it doesn't specify.

    So we could assume there is no key (or the whole record is a key) but then "the records that exist in both versions of the tables are different between the two versions." wouldn't make sense because if any part of the record is different then it would qualify as a different record.

    Or we could assume that one of the columns (probably Col1) is a key but then "Does your query let you know which field changed?" doesn't make sense because the only column that could change is the non-key field.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Jan 2004
    Location
    Southern California
    Posts
    4,750

    Re: A query to compare two almost identical tables?

    I realize this and told the author. This was a question on a programming test for a job. Sounds like they don't know SQL! LOL
    Blake

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.