Ashworth Ashworth - 6 months ago 6
SQL Question

Query for records that are the same in one field, but different in another?

I am trying to write a SQL query to extract records from a single table that have the same model_number field, but different primary_use fields. I have been able to query for duplicates, but my query makes no distinction if a record is truly a duplicate or if primary_use is different. I'm not interested in where records have the same information in both fields, only where there is a mis-match.

Answer

With SQLite I believe your best bet is a self join:

SELECT DISTINCT t1.*, t2.*
FROM yourtable as t1
    INNER JOIN yourtable as t2 ON
        t1.model_number = t2.model_number AND
        t1.primary_use <> t2.primary_use;

Joining the table to itself where model_number is the same, but primary_use is different and then returning the distinct results should give you what you are after.

Comments