Re: self referential database table

aakbar@xxxxxxxxx wrote:

is there a way without adding a column or what i am thinking to just
reply on relationship between emp_id with itself is not sufficient.

You need some column that determines which row contains the current
data for a given emp_id - e.g. 'date_inserted', or 'is_active', or
an IDENTITY column. Rows are not inherently sorted in order of
insertion (Celko: "rows are not records") - you have to impose a
sort order based on one or more columns, which can match order of
insertion if you configure things to that end.

I agree (as I think I mentioned previously) with Erland's suggestion
of keeping current data in one table and history in another; failing
that, creating a current_data view and using it consistently.
.

Relevant Pages