Sophisticated versioning with tables
Posted: Wed Feb 24, 2010 11:08 am
I have a table sequences, which looks like:
The fields id_previous and id_task are used for a quasi-version control. Essentially records with id_previous of ZERO are orginal records, whereas a user can clone a record and the id_previous of that record points to the original in the table.
There might be three duplicate entires in the table, like so:
I need a query (somehow) to SELECT all records by default id_task equals ZERO, which is easy.
Here is the challenging caveat: I only wish to show those whom have a id_task of ZERO when a record whose id_previous to match, does not exist.
So essentially the current SQL looks like:
[sql]SELECT * FROM sequences WHERE id_task = $id_task OR (id_task = 0 AND id_previous != id_primary)[/sql]
Obviously this SQL does not work, I assume I probably need a sub-select. I am reading this article provided the other day by pytrin:
http://www.xaprb.com/blog/2006/12/07/ho ... up-in-sql/
However I cannot seem to put pieces togather and figure out a way to essentially remove duplicates using the logic/specifications above.
Any ideas??
In PHP this could be done easily by iterating the list and checking for any records whose id_primary is also referenced in any other records id_previous, which is the cloned copy I need shown. I would rather have this done in SQL not PHP.
Cheers,
Alex
Code: Select all
id_primary, id_previous, id_task, descriptionThere might be three duplicate entires in the table, like so:
Code: Select all
1, 0, 0, "This is a original description"
2, 1, 1, "This is a original description -- modified by a user"
3, 1, 2, "This is a original description -- modified again by a user"Here is the challenging caveat: I only wish to show those whom have a id_task of ZERO when a record whose id_previous to match, does not exist.
So essentially the current SQL looks like:
[sql]SELECT * FROM sequences WHERE id_task = $id_task OR (id_task = 0 AND id_previous != id_primary)[/sql]
Obviously this SQL does not work, I assume I probably need a sub-select. I am reading this article provided the other day by pytrin:
http://www.xaprb.com/blog/2006/12/07/ho ... up-in-sql/
However I cannot seem to put pieces togather and figure out a way to essentially remove duplicates using the logic/specifications above.
Any ideas??
In PHP this could be done easily by iterating the list and checking for any records whose id_primary is also referenced in any other records id_previous, which is the cloned copy I need shown. I would rather have this done in SQL not PHP.
Cheers,
Alex