SQL: Compare rows in a same table

I'm trying to compare rows in a single table and figure out if "addr" and "zip" under the same id are same or different.

  id  |   addr   |   zip
------+----------+----------
  1   |   123    |   0000     
  1   |   123    |   0000    
  1   |   123    |   0001    
  2   |   222    |   1000    
  2   |   221    |   1000

So the result should say id 1 has valid addr and invalid z开发者_如何学Goip id 2 has invalid addr and valid zip.

Any hint will be appreciated! Thank you!!

The query...

SELECT id, COUNT(DISTINCT addr), COUNT(DISTINCT zip)
FROM YOUR_TABLE
GROUP BY id

...should give the following result on your example data...

1, 1, 2
2, 2, 1

The numbers in bold greater than 1 indicate "invalid" items.

If you want to actually filter on this, you can use HAVING clause, for example:

SELECT id, COUNT(DISTINCT addr) ADDR_COUNT, COUNT(DISTINCT zip) ZIP_COUNT
FROM YOUR_TABLE
GROUP BY id
HAVING ADDR_COUNT > 1 OR ZIP_COUNT > 1

May I suggest that if you don't actually want this kind of "mismatched" data in your database, redesign your data model so duplicates cannot happen in the first place. No duplicates, no mismatches!

Group by id. Select id, COUNT(DISTINCT addr) and COUNT(DISTINCT zip) columns. Filter the rows where the number of distinct address or zips > 1.
This will give you the ids with inconsistent duplicate data.

Example:

SELECT id, COUNT(DISTINCT addr) nAddr, COUNT(DISTINCT zip) nZip
FROM [mytable]
GROUP BY id
HAVING nAddr > 1 OR nZip > 1

Cheers,

SELECT id
       , CASE s.addrcount 
         WHEN 1 THEN 'valid' 
         ELSE 'invalid' END as addrok
       , CASE s.zipcount 
         WHEN 1 THEN 'valid'
         ELSE 'invalid' END as zipok
FROM 
(
  SELECT id
         , count(distinct addr) as addrcount
         , count(distinct zip) as zipcount
  FROM table1
  GROUP BY id
) as s