On Thu, Mar 27, 2008 at 12:59 PM, kdecapite <[EMAIL PROTECTED]> wrote: > > I may have not explained the setup properly. I don't actually have a > "full_name" field in either table (import_contacts and contacts are > the actual MySQL table names). Each of these tables have "fn" and "ln" > fields, however. So the problem is that I need to somehow find all > records in the contacts table which have the same values in the "fn" > and "ln" fields as the import_contacts table. >
I could be misinterpreting your needs, but ... SELECT c.id, c.fn, c.ln FROM contacts AS c INNER JOIN import_contacts AS ic ON c.fn = ic.fn AND c.ln = ic.ln; This will select the id, fn, and ln from contacts where it also exists in import_contacts. > The only idea I have at the moment is to create a field in both tables > which simply stores the concatenated value of a record's "fn" and "ln" > fields. This would require me to write a script to retro-fit the > existing 30k contact records, as well as update some logic in my "add > contact" controller. Not to mention this would also create a redundant > field in the table and still doesn't ultimately solve the problem > because what if in the future I want to find duplicates matching "fn", > "ln" and "mi" (middle initial)? I would have to create yet another > "dummy" field in my table. > UPDATE TABLE contacts ADD COLUMN full_name VARCHAR(128); -- or whatever size UPDATE TABLE contacts SET full_name = CONCAT_WS(' ', fn, ln); BUT, you'd probably be far better off simply creating an index on the tables: CREATE INDEX contacts_fullname ON contacts (fn, ln); CREATE INDEX import_contacts_fullname ON import_contacts (fn, ln); This is all for MySQL, which i'm assuming you're using. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---