CODEWITHSUNDEEP
mysqlsql
The87Boy
The87Boy

Reputation: 887

Check for one-on-one relation - SQL

I have downloaded a file containing zip-codes and councils, where I have to check if all councils have a one-on-one relationship with its council-number (as I then can store it in multiple tables to save space)

The table right now looks like

councilnumber, int(3)
council, varchar(25)
zip, int(5)

How can I check if it has a one-on-one relationship?

Upvotes: 0

Views: 511

Answers (2)

ULazdins
ULazdins

Reputation: 2025

SELECT councilnumber, count(council) as `count` FROM Councils group by councilnumber order by `count` desc

and 

SELECT council, count(councilumber) as `count` FROM Councils group by council order by `count` desc

If the first row has count higher than 1, that means you have more than one value for the council number or council

Upvotes: 0

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

SELECT MIN(councilnumber), MAX(councilnumber), zip FROM tble group by councilnumber, council, zip HAVING MIN(councilnumber) <> MAX(councilnumber)

This will show bad records. If you need to discard them then use:

SELECT MIN(councilnumber), MAX(councilnumber), zip FROM tble group by councilnumber, council, zip HAVING MIN(councilnumber) = MAX(councilnumber)

By the way I am not sure that 1to1 is the right relationship. County can have a lot of zips in my mind. And in reality some zips can belong to 2 counties (at least in USA)

In that case I would suggest you to use another table that would have link to ZIP and COUNTY with it's own ID. Then you'll be able to get ZIP and county from that ID and it will support many-to-many relationship

Upvotes: 1

Related Questions