StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
13073452
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2012-10-25T16:52:46.380
FavoriteCount
0
LastActivityDate
2012-10-25T22:51:03.243
LastEditDate
2012-10-25T22:51:03.243
LastEditorUserId
1769720
OwnerUserId
1769720
ParentId
12983252
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
There's no specific join type to handle that sort of query. You have to do it either as a complex query or programmatically. Below is an example of PL/PGSQL code for this problem, using your example data. <pre><code>CREATE OR REPLACE FUNCTION getRev(OUT rev INT, OUT A INT, OUT B CHAR, OUT C INT, OUT D BOOL) RETURNS SETOF record STABLE AS $BODY$ DECLARE c1 SCROLL CURSOR FOR SELECT * FROM Table1 ORDER BY rev; c2 SCROLL CURSOR FOR SELECT * FROM Table2 ORDER BY rev; r1 Table1%ROWTYPE; r1c Table1%ROWTYPE; r2 Table2%ROWTYPE; r2c Table2%ROWTYPE; BEGIN OPEN c1; OPEN c2; FETCH c1 INTO r1; FETCH c2 INTO r2; r1c := r1; r2c := r2; WHILE r1 IS NOT NULL AND r2 IS NOT NULL LOOP CASE WHEN r1.rev = r2.rev THEN rev := r1.rev; A := r1.a; B := r1.b; C := r2.c; D := r2.d; FETCH c1 INTO r1c; FETCH c2 INTO r2c; CASE WHEN r1c.rev = r2c.rev THEN r1 := r1c; r2 := r2c; WHEN r1c.rev < r2c.rev THEN r1 := r1c; FETCH PRIOR FROM c2 INTO r2c; ELSE r2 := r2c; FETCH PRIOR FROM c1 INTO r1c; END CASE; WHEN r1.rev < r2.rev THEN WHILE r1c IS NOT NULL AND r1c.rev < r2.rev LOOP r1 := r1c; FETCH c1 INTO r1c; END LOOP; rev := r2.rev; A := r1.a; B := r1.b; C := r2.c; D := r2.d; r1 := r1c; ELSE WHILE r2c IS NOT NULL AND r2c.rev < r1.rev LOOP r2 := r2c; FETCH c2 INTO r2c; END LOOP; rev := r1.rev; A := r1.a; B := r1.b; C := r2.c; D := r2.d; r2 := r2c; END CASE; RETURN NEXT; END LOOP; CLOSE c1; CLOSE c2; RETURN; END $BODY$ LANGUAGE 'plpgsql'; </code></pre> This should run in O(length(Table1) + length(Table2)). Note the tricky part in the "CASE WHEN r1.rev = r2.rev" : we have to choose on which table we continue the scan for the next iteration. The correct one is the one with the smallest rev value after the cursor, to get through all the rev numbers available in both tables. You could certainly get better performance by coding it in C or C++. 
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POMerging two version-tracking tables while filling in values
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USdidierc
UserOwnerUserId
1. USdidierc
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.