Re: Query through two tables

put user_id in the contact table as a fk. good luck. "Michael P. Mehl" <abo@silentpower.de> wrote in message news:002901c0f96c$908a97c0$0a00a8c0@sokrates... > Hi! > > I've got two tables: > > CREATE TABLE users ( > user_id bigint(20) unsigned DEFAULT '0' NOT NULL, > contact_id bigint(20) unsigned DEFAULT '0', > user_login varchar(100) NOT NULL, > PRIMARY KEY (user_id), > UNIQUE UC_user_id (user_id) > ); > > CREATE TABLE contacts ( > contact_id bigint(20) unsigned DEFAULT '0' NOT NULL, > contact_type varchar(100) NOT NULL, > contact_value mediumtext, > PRIMARY KEY (contact_id, contact_type), > KEY idx_contacts (contact_id, contact_type) > ); > > In the table "users" the field "user_id" is a unique key for every user, > "user_login" contains an identification string to login into an system. > "contact_id" is the id of the entries in the table "contacts" which > contain more information about the user. > > The special thing about this is that there are more than one entries in > the table "contacts" for each user. For example to store the prename of > a user, the following entry exists: > > INSERT INTO contacts VALUES ( '1', 'person/prename', 'Michael > P.'); > > At the SAME time there's a second entry in the table "contacts" for the > user's surname: > > INSERT INTO contacts VALUES ( '1', 'person/surname', 'Mehl'); > > The problem: How is it possible -- for example using temporary tables -- > to do a query whose result is a table with the fields "user_login" and > two other fields "user_prename" and "user_surname" containing the > corresponding values from the table "contacts". > > Best regards and thanks in advance... > Michael > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net >