StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFind row-sets with mismatched values without large self join?
primarykey
Id
8259810
data
AcceptedAnswerId
8259920
AnswerCount
5
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2011-11-24T16:07:30.690
FavoriteCount
0
LastActivityDate
2011-11-24T16:28:35.107
LastEditDate
LastEditorUserId
0
OwnerUserId
321013
ParentId
0
PostTypeId
1
Score
2
ViewCount
929
LastEditorDisplayName
text
Body
I have a table like this (basic example, not the real thing): <pre><code>FKEY | NAME | ATTRIBUTE_X -------------------------- 1 '...' 42 1 '...' 42 1 '...' 42 2 '...' 7 2 '...' 7 5 '...' 42 5 '...' 42 5 '...' 42 5 '...' 42 6 '...' 300 6 '...' 300 .... </code></pre> Where - normally - each of the attribute_x values for a given fkey are all the same. (In my real data, I calculate attribute_x from some columns in the table and this property needs to be the same for all rows with the same fkey. Now I have some rows where this property does not hold. I want to search the whole table to find all FKEYs with mismatched attribute_x values. Example: <pre><code>-------------------------- 145678973 '...' 23 145678973 '...' 22 // Error, should also be 23 145678973 '...' 23 </code></pre> My naive approach was: <pre><code>SELECT distinct(TX1.FKEY) FROM TABLEX TX1, TABLEX TX2 WHERE TX1.FKEY=TX2.FKEY AND TX1.ATTRIBUTE_X <> TX2.ATTRIBUTE_X ; </code></pre> But with my real data this doesn't complete (I ran of of temp tablespace and after the DBA increased the temp tablespace to 20 GIG the query ran for a few hours and then bailed out.) Generally, is there a more efficient query for this? <hr> I have a solution with PL/SQL where I just loop over the table sorted by FKEY, and if I find a different attribute_x vs. the last fetched record where the fkey stayed the same, I have found an erroneous fkey. But this seems oh so primitve :-) Is there an efficient pure SQL solution?
Tags
<sql><oracle><query-optimization>
Title
Find row-sets with mismatched values without large self join?
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USMartin Ba
plurals
PostLinksPostIdRelatedPostId
1. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POFind row-sets with mismatched values without large self join?
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
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.