Select from audit table

Question

I have following table

column1 column2 newvalue oldvalue

in columns newvalue oldvalue are values (foreign keys) from other table. I need display some other column from referenced table(i.e. based on primary keys). So in select instead of newvalue as some key will be displayed some column which coresponds to the keys. I am new here, so if you want to clarify my question more, i will do it. Thanks in advance.

Edit: In first table are columns entityID(from audited table), columnName, OldValue, NewValue

second table AddressId, city, street, ZIPCode.

In first table in colmns newvalue, oldvalue are PKs from second table. I need display for example oldstreet, new street.

please add the tables involved. if possible also include the columns. — John Woo, Apr 05 '13 at 06:33

score 1 · Answer 1 · answered Apr 05 '13 at 06:49

What you need is to JOIN tables together. There are different types of JOIN, for your case you need to use INNER JOIN, the reason is the column newvalue and column oldvalue are Foreign Keys. You can join two tables like following sample:


select column1, column2,
       B.newcolumn1, -- from table B
       c.newcolumn2,  -- from table C
FROM tableA A
inner join tableB B
   on A.newvalue = B.newvalue -- join on FK = PK
inner join tableC C
  on A.oldvalue = c.oldvalue

You can google how to use JOIN Here is a LINK

Select from audit table

1 Answers1