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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
