Hi Larry, I like your solutions, but what do you do if you have both male & female Rabbis?
Dave Billing ----- Original Message ----- From: "Lawrence Lustig" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, November 26, 2002 11:19 AM Subject: Re: IFIN function possibility > > > Larry, would elaborate a bit on "the separate table > > method"? > > In Duey's original example, Title is a column that > specifies the honorific to be applied to the person's > name (Mr., Ms., Mrs., etc). He wants to display Male > next to those names that have a 'Male' honorific and > 'Female' next to the names with a female honorific. > > This is a typical look up table or PK/FK situation if > all possible honorifics can be known (such as Rabbi, > Sir, etc). So it seems to me the cleanest > implementation, instead of doing the look up in a > pseudo-Case statement built into a function in a > SELECT statement, would be to put the information in a > separate table and look it up by joining that table > with the query in question: > > CREATE TABLE TitleLookups ( + > Title CHAR(8), + > Gender CHAR(6), + > ) > > SELECT MyQuery.*, TitleLookups.Gender + > FROM MyQuery RIGHT OUTER JOIN TitleLookups + > ON MyQuery.Title = TitleLookups.Title > > (You need to do the outer join if you are going to > allow Titles in the underlying table that are not in > the TitleLookups table. Otherwise you can use an > inner join). > > I like this solution better because: > > - It uses the database to a database lookup > function. > - It allows the addition of new titles with their > associate gender information without having to change > the program code. > - It provides a single centralized source of gender > information so that there cannot be discrepancies or > omissions in reportng gender in different parts of the > program. > > -- > Larry > > > > > __________________________________________________ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
