StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Does Age, name, profession form a primary key (or at least a unique key?):</p> <p>If so, you could do something like:</p> <pre><code>SELECT t1.name, t1.age, t1.profession, t1.column1 as t1column1, t2.column1 as t2column1, t1.column2 as t1column2, t2.column2 as t2column2, FROM table1 t1, table2 t2 WHERE (t1.name = t2.name and t1.age = t2.age and t1.profession = t2.profession) and ( t1.column1<>t2.column1 OR t1.column2<>t2.column2 OR t1.column3<>t2.column3) </code></pre> <p>Of course, that requires a unique key which is the same across both tables. Also, I clearly changed the results of the query to show all columns instead of just the one that changed. Identifying the one that changed like that in a single T-SQL Query is awkward (but possible) so my recommendation would be to return it like this and depending on your use case have the application/presentation layer handle finding which column changed or just scan it by eye.</p> <p>If you really want to do it in T-SQL, you could do it with UNIONS, like:</p> <pre><code>SELECT t1.name, t1.age, t1.profession, 'column1' as ColumnChanged, t1.column1 as oldValue, t2.column1 as newValue FROM table1 t1, table2 t2 WHERE (t1.name = t2.name and t1.age = t2.age and t1.profession = t2.profession) and t1.column1<>t2.column1 UNION ALL #better peformance than UNION, which would enforce uniqueness SELECT t1.name, t1.age, t1.profession, 'column2' as ColumnChanged, t1.column2 as oldValue, t2.column2 as newValue FROM table1 t1, table2 t2 WHERE (t1.name = t2.name and t1.age = t2.age and t1.profession = t2.profession) and t1.column2<>t2.column2 ....... </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload