On Fri, Jun 18, 2010 at 1:11 PM, Ken Kixmoeller f/h <[email protected]> wrote: > Hi, PFers - -- - > > In my normalization attempts, I often create a "Means of Contact" (MOC) > entity. It is a variation of the old "how do you handle telephones" > conundrum (which usually leads to a flame war <g>).
Yeah, long debates go on about people and contacts all the time. I'm a fanatic for proper data design but will hold my peace unless asked. SELECT People.*, NVL(MOC.medium, "telephone"), NVL(MOC.address, "000-000-0000") FROM People LEFT OUTER JOIN MOC on People.person_id = MOC.person_id AND Moc.medium = "telephone" WHERE... The Left Outer Join includes all left-side (People) records joined with a MOC if found, or NULL if not. The AND clause on the JOIN only allows through MOC records of type telephone. The NVL wallpapers over the fact that a record wasn't found by faking an empty entry. -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
