Find difference between two records

Question

Find difference between two records

MuhammadShafiq

SSCrazy

Points: 2509
More actions
March 12, 2016 at 12:24 am

#308939

I need to keep history for each record in my table. I created another same structured table with action field which mentions its insert or update.Here is my structure
CREATE TABLE IF NOT EXISTS `crm_listings_history` (
`id` bigint(20) unsigned NOT NULL,
`user_id` int(11) NOT NULL,
`ref` varchar(55) NOT NULL,
`name` mediumtext NOT NULL,
`description` longtext NOT NULL,
`beds` varchar(22) NOT NULL,
`baths` int(11) NOT NULL DEFAULT '1',
`unit` varchar(55) NOT NULL,
`size` int(11) NOT NULL,
`price` decimal(18,2) NOT NULL,
`dateadded` datetime NOT NULL,
`listing_id` int(11) NOT NULL
)
Where listing_id is the id of crm_listings table.Now i want to get result like
UserID | Action | DateAdded |Field | Ref
1 | Insert | 21/05/2015 | Id | ABC
1 | Update | 21/05/2015 |Price | ABC
how i can write this query? How i can find which field has been updated?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

looking at your table definition, it would seem that you are not using MS SQL...(.MySQL maybe?)

this site is for Microsoft SQL predominately ....you may find you get better answers in a forum dedicated to your database.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 2

I just created this structure as available tool to give understanding of my question.I have to use SQL.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

ok....please read

http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

then post back with create table statements, sample data and expected results as shown above.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

just came across this....seems quite similar, but states MySQL

http://stackoverflow.com/questions/35917478/find-difference-between-two-records-in-mysql

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

The question sounds like you want to create an audit table. Like J Livingston pointed out, this is a SQL Server forum.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

The Danish Dynamo SSC-Addicted Points: 464 More actions · Answer 6

It depends on what version you are using

MSSQL 2014+ has that feature:

https://msdn.microsoft.com/en-us/library/bb933994.aspx

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

The Danish Dynamo (3/14/2016)
It depends on what version you are using
MSSQL 2014+ has that feature:
https://msdn.microsoft.com/en-us/library/bb933994.aspx

Here's why I don't use that feature...

•There is a built-in cleanup mechanism. Cleanup for change tracking is performed automatically in the background.

That violates the requirements placed upon me to keep audit data forever. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)