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>).
This MOC entity works well in a number of client systems, especially those in which we keep track of home & work phone #s, fax #s, cell #s and email addresses. (Yes, an e-mail address is a MOC parallel to a phone number). So the table looks like this: moc_id person_id moc_type moc_medium moc_address 1 1 Work e-mail [email protected] 2 1 Work telephone 612-555-1212 3 2 Home e-mail [email protected] 4 3 Home telephone 507-555-1345 5 3 Work e-mail [email protected] 6 3 Work facsimile 507-555-6511 However, I have encountered a situation where the Accounting department of my client needs a file with the phone number concatenated to the contact's name in a field. The problem comes when there is not a telephone record for the person in the MOC table. (Many of these data are converted, and the telephones didn't come over well. New records require a telephone. Notice person_id 3 has no telephone record. Person 4 may not have *any* MOC records.) SO, I need a SQL statement to do it. I have it all working OK, as far as the joins, but when there is no record, or no "telephone" record it gets messed up. So: if I do: ...where moc_medium = 'telephone' -- people without telephone records are excluded ...group by (to get one record per person) ...order by moc_medium DESC Obviously I get the first entered MOC record (I know the SQL doesn't process that in the right order, but I thought I'd try anyway.) It would be OK to see an e-mail when the person has no phone. So I'm stuck -- Can I do an IF in a where statement? Something like: ...where if (moc_medium is not NULL then moc_medium = 'telephone') I don't think posting the whole dang SQL statement will help you all. It is about 50 lines long (full of cases and ifs and concat()s, and it joins 8 or 9 tables with mixed innies and outies). All of that stuff is working great. It is just this one issue... Ideas or suggestions? It is possible, of course, to do this in code as opposed to SQL, but I'd rather find a SQL solution. Thanks - -- - -- - - - - - - - Ken _______________________________________________ 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.
