On Jan 25, 2007, at 11:46 AM, Kai Schlamp wrote:
What is the best way to find out the relationship of two columns in a table? I want to know if it is 1:1, 1:N, or M:N. Is there an easy way to do this with a SQL statement?
If both tables have the same rowcount, and SELECT count(*) FROM a JOIN b USING ... has the same count then it's 1:1 (if by 1:1 you mean there must be a record for each value in both tables).
IF SELECT count(*) FROM b WHERE NOT EXISTS (SELECT * FROM a WHERE a.field = b.field) is 0 and SELECT count(*)=count(DISTINCT field) FROM a is true, then it's 1:N (a:b).
There might be some clever tests you can do with INTERSECT and the like, too.
-- Jim Nasby jim@xxxxxxxxx EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
