Hello I have two tables:
- Phones, which has two columns: Tel and Name. A customer may have more than one number - Contacts, which has the expected colums (address, e-mail, etc.), and, since phone numbers are unique, uses the main (or only) number in Phones as primary key. IOW, to find a contact's name, I take its phone number in Contacts.Phones_Tel, look it up in table Phones, and fetch its Phones.Phones_Name. I'd like to create records in table Contacts by reading each record in Phones, and adding those records into Contacts that don't exist yet. Since the syntax "WHERE phones_contact_tel not in contacts;" doesn't seem to exist, I assume I must use OUTER JOIN, but I've never used this before. => Can someone tell me how to read each line in Phones and Contacts, extract those that exist in Phones but not in Contacts, and insert those into Contacts? Thank you. Here's the schema: sqlite> CREATE TABLE phones (phones_tel TEXT PRIMARY KEY NOT NULL, phones_name TEXT NOT NULL); sqlite> CREATE TABLE contacts (contacts_phones_tel TEXT PRIMARY KEY NOT NULL, contacts_address TEXT, contacts_zip TEXT, contacts_city TEXT, contacts_fax TEXT, contacts_email TEXT, contacts_comment TEXT); sqlite> CREATE TEMP TABLE phones_contacts (contacts_phones_tel TEXT PRIMARY KEY NOT NULL, contacts_address TEXT, contacts_zip TEXT, contacts_city TEXT, contacts_fax TEXT, contacts_email TEXT, contacts_comment TEXT); (what I'd like to do) sqlite> INSERT INTO phones_contacts SELECT phones_tel,NULL,NULL,NULL,NULL,NULL,NULL FROM phones,contacts WHERE phones_contact_tel not in contacts; _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
