MySQL: check whether two fields on a model are always the same?

Question

Sorry if this is a duplicate question, I'm a total database newbie and I'm probably using the wrong terminology to search for answers.

I have a MySQL table as follows:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| placeid    | int(11)       | NO   | PRI | NULL    |       |
| grid       | varchar(120)  | YES  |     | NULL    |       |
| vill       | varchar(300)  | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

I'd like to find out whether 'grid' and 'vill' always occur in the same combinations or not.

Maybe it'd be clearer with an example:

placeid, grid, vill
1,       TM1,  Suffolk
2,       TM1,  Suffolk
3,       WA8,  Newcastle
4,       WA8,  Newcastle
5,       WA8,  York

I'd like to construct a query that returns 'WA8' but not 'TM1', because 'WA8' occurs in combination with more than one vill.

I would be SO grateful for any help!

Answer 1

You can do it like this:

SELECT grid
FROM MyTable
GROUP BY grid
HAVING COUNT(DISTINCT vill) > 1

It selects the grids for which there is more than one distinct vill.