Essentially yes, I just wasn't sure what exactly his criteria for matching was -- if he (you?) needed an exact match including the suffix or if he needed to match only the last name... You'll have to forgive me for not knowing who made the original post, I'm a bit distracted -- tired from walking home since my car is at the mechanic and in a hurry to call a potential client.
> That's a great idea, do the join on a calculated field! > ON OnlyLastName + ' ' + Suffix = LastNameAndSufix > Is that what you're suggesting? If so and it works, I like > :O) > Ade > -----Original Message----- > From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] > Sent: 23 February 2005 23:45 > To: CF-Talk > Subject: Re: OT-SQL matching >> For a CF page I am doing I need to out put matched >> results. However in one table I have last names with a >> suffix like "MOLINA JR" and in another table the last >> name >> and suffix are in different columns. I need to strip out >> the suffixes as well as the preceding space in the first >> column. Any ideas. I am trying a few thing and it is not >> working. > offhand > left join tabl2 on (table2.lastname = table1.lastname > or table2.lastname like table1.lastname + ' %') > or if you want to match both > left join table2 on ( > (table2.lastname = table1.lastname and table2.suffix is > null) or > (table2.lastname + ' ' + table2.suffix = > table1.lastname)) > This is sql server syntax... it's gonna be different for > every db... > it's an ugly, PURE EVIL BLACK AS COAL schema you have for > the data > there... I have to deal with those at my job too -- hate > 'em... s. isaac dealey 954.927.5117 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/story/?storyid=44477&DE=1 http://www.sys-con.com/story/?storyid=45569&DE=1 http://www.fusiontap.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:196239 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
