Looking back query

[CryoGen]

I have two tables, WIDGETS and VERSIONS. The WIDGETS table has descriptive information about the widgets while the VERSIONS table contains IDs relating to different iterations of those widgets over the years. The idea is that from any widget in the database you can look forward and backward to see different versions of the widget through the years. Here are the tables:

WIDGETS
widget_id
name
description
year

VERSIONS
v_id
new_id
old_id

Now my query gets me somewhere in the vicinity, but not where I want to be.

select      w.widget, w.widget_id, v.new_id, v.old_id
from        widgets w, versions v
where       w.widget_id = 712
and         w.widget_id = v.new_id
order by    w.widget;

... and I get ...

WIDGET WIDGET_ID NEW_ID OLD_ID
Widget 93 712 712 535
Widget 93 712 712 287
Widget 93 712 712 1
Widget 93 712 712 205
Widget 93 712 712 458

What I would like is a list that shows the widget names related to OLD_ID, rather than NEW_ID. But I'm not sure how to get there.

 

[WebmasterA]

If I understand the question correctly than the answer is:

select w.widget, w.widget_id, v.new_id, v.old_id
from widgets w, versions v
where w.widget_id = 712
and w.widget_id = v.old_id
order by w.widget;

 

[CryoGen]

OK, I think I see where I left something out. The old_id column is never going to have the current widget_id (e.g., 712) in it. Each widget has the other iterations of that widget assigned individually, and the new_id column is used to tie that record back to it.

Am I making sense or confusing the hell out of you? I do appreciate the help.

 

[WebmasterA]

I think I know what you mean.
Each time you create a new widget you assign the new_id to it and you have old_id of the previous widget that is associated with the new widget.
Is that correct?

 

[CryoGen]

Right ... each new widget gets a new ID and the IDs of the old widgets are assigned to it in the versions table.