How to do an Inverse Union... ideas?

[sedarious]

Here is an interesting question... How do you do the INVERSE of a distinct union. AKA I have 2 sets of data and I want to find out what is DIFFERENT in the two (logically equvalent to a subtraction). I know I could do analysis externally with php or something, but I am curious if there is a way to do it internal to mysql (preferably not mysql specific - but rather general sql).

[sedarious]

I got it to work. I had to use temporary tables though. I took the two data sets and did a (i!=i)||(j!=j)||(k!=k) (basically took out all of the colums that are identical) and added a new column that is fulled with ones. I then did a groupby i,j,k and summed up the ones - this gives me a total of non-equal matches. I then found the max of non equal matches. I then compared the sums of non-equal matches to the max non-equal matches and this gives me the data set I am looking for. It would be nice if there was just a subtract to mirror the union.

[Bradmont]

The set operation you want to use here is a difference, which may or may not be implemented in your DBMS of choice (with MySQL, most likely not). Anyway, check the docs, you might find a much easier way. :)

[BsCis99]

BsCis99: If you have a programming language that will work with SQL you can do this in code. Well, at least if I am following what you are trying to do. What I think you are trying to do is compare two tables with the same structure to retrieve records where one or more of the related records' field values differ. The following should work in Visual FoxPro.


USE firsttable IN 0
USE secondtable IN 0

lcWhereClause = " WHERE "
FOR lnField = 1 TO FCOUNT()
lcWhereClause = lcWhereClause + FIELD(lnField) + " != " + FIELD(lnField) + " OR "
ENDFOR
lcWhereClause = LEFT(lcWhereClause, LEN(lcWhereClause) - 4)

SELECT * ;
FROM firsttable INNER JOIN secondtable ON firsttable.keyfield = secondtable.keyfield ;
WHERE &lcWhereClause