Need to write a sql query: There can only be one value for another particular value

For ex开发者_如何学Pythonample, in the table ADDRESSEs there is a column ZIP_CODE. The zip codes can be anything, say 90210, 45430, 45324. There can be multiple instances of 90210 or any other zip code. But for any zip code, there can only be one value for it in the STATE column. If 90210 is in the zip code column, the STATE column MUST be CA, if there is another record with 90210 and has OH or GA or anything else, it is incorrect. I am looking to find these particular zip codes that have anything other than one single value in this other column. This is not a homework question.

select zip_Code, count(distinct state)
from address
group by zip_code
having count(distinct state) > 1;

try this:

select zip_code, count(*)
  from (select distinct zip_code, state
          from address)
 group by zip_code
having count(*) > 1;

The clearest way to design this is to have a table that contains every allowable combination of ZIP code and state, then use those two columns as foreign keys against the table that contains the actual address. This way, any invalid state/ZIP combination would violate the foreign key constraint. This approach will work even if a ZIP code crosses state lines, as long as you don't make the ZIP code field of the lookup table a unique key. The downside to this approach is that you would need to pre-populate the lookup table and keep it up-to-date, which would mean paying the USPS for their listings.

If you're really insistent on the one-state-per-ZIP approach and/or don't want to buy the list from the USPS, you could still use a similar approach. Again, you'll want a lookup table with the state and ZIP, but this time you'll want to make the ZIP a unique key. As records are added to the table containing addresses, you could use a trigger to populate the lookup table when the ZIP code doesn't already exist. This would add a little overhead, but not enough to worry about in most cases.

select count(*), zip_code 
from address 
group by zip_code 
having count(*) > 1