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

[blakemckenna]

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,

[jmcilhinney]

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:

SELECT *
FROM Table1 t1 INNER JOIN Table2 t2
ON t1.Id = t2.Id
WHERE t1.Column1 <> t2.Column1
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.

[sapator]

Maybe

select t1 from Table1
EXCEPT
select t2 from table2

?

[FunkyDexter]

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.

[blakemckenna]

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