Sql server® 2012 t-sql fundamentals

 

CHAPTER 3

 

Joins

 

 107

Each audit row stores a log serial number (lsn), the key of the modified row (orderid, productid), the 

name of the modified column (columnname), the old value (oldval), the new value (newval), when the 

change took place (dt), and who made the change (loginname). The table has a foreign key defined on 

the attributes orderid, productid, referencing the primary key of the OrderDetails table, which is defined 

on the attributes orderid, productid. Assume that you already have in place in the OrderDetailsAudit 

table a process that logs, or audits, all changes taking place in column values in the OrderDetails table. 

You need to write a query against the OrderDetails and OrderDetailsAudit tables that returns 

information about all value changes that took place in the column qty. In each result row, you need 

to return the current value from the OrderDetails table and the values before and after the change 

from the OrderDetailsAudit table. You need to join the two tables based on a primary key–foreign key 

relationship, like this.

SELECT OD.orderid, OD.productid, OD.qty, 

  ODA.dt, ODA.loginname, ODA.oldval, ODA.newval 

FROM Sales.OrderDetails AS OD 

  JOIN Sales.OrderDetailsAudit AS ODA 

    ON OD.orderid = ODA.orderid 

    AND OD.productid = ODA.productid 

WHERE ODA.columnname = N'qty';

Because the relationship is based on multiple attributes, the join condition is composite.