Compare rows in a table for differences between fields

Any expression in SQL must refer to columns in only one row (no subqueries).

A JOIN

can be used to create two different rows on the same row of a result set.

This way you can compare values ​​in different strings by doing the concatenation yourself. Here's an example that shows the concatenation of each row to all other rows associated with the same customer (excluding joining the row to itself):

SELECT c1.*, c2.*
FROM client c1
JOIN client c2 ON (c1.clientID = c2.clientID AND c1.id <> c2.id)

      

        
        
        
      

    

You can now write expressions that compare columns. For example, to restrict the above query to those where field1 is different:

SELECT c1.*, c2.*
FROM client c1
JOIN client c2 ON (c1.clientID = c2.clientID AND c1.id <> c2.id)
WHERE c1.field1 <> c2.field1;

      

        
        
        
      

    

You don't specify which comparisons you should make, so I'll leave that to you. The key point is that in general, you can use self-join to compare strings in a given table.

Repeat your comments and explanations: Okay, your "difference" is not just in value, but in the ordinal position of the string. Remember that relational databases have no concept of line numbers, they only have the order of the lines relative to some order that you must specify in the sentence ORDER BY

. Do not confuse the alias " id

" with the line number, numbers are assigned as monotonically increasing only when their implementation coincides.

In MySQL, you can use user-defined variables to achieve the effect you are looking for. Order the query clientId

and then id

track the values ​​for each column in the MySQL user variables. When the value on the current line is different from the value in the variable, do what you highlight, what you are going to do. I'll show an example for one field:

SET @clientid = -1, @field1 = '';
SELECT id, clientId, field1, @clientid, @field1,
  IF(@clientid <> clientid, 
    ((@clientid := clientid) AND (@field1 := field1)) = NULL,
    IF (@field1 <> field1, 
      (@field1 := field1), 
      NULL
    )
  ) AS field1_changed
FROM client c
ORDER BY clientId, id;

      

        
        
        
      

    

Note that this solution is not much different from simply fetching all rows with plain SQL and tracking the values ​​using application variables while fetching the rows.