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 [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
