Check out the MySQL join syntax. I wish I had time to offer more help, but I'm running out to a meeting. http://www.mysql.com/doc/en/JOIN.html Edward Dudlik Becoming Digital www.becomingdigital.com ----- Original Message ----- From: "Micah Bushouse" <mbushouse@hotmail.com> To: <php-db@lists.php.net> Sent: Sunday, 11 May, 2003 23:36 Subject: Is this possible in a single query? Dear list, I'm having a problem selecting two different indexes from a table and putting them into different columns in my results. It's hard to describe this problem, so I'll try to show it. Here is the specs of the tables in question. CREATE TABLE `vote_elections` ( `vote_election_id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `user_id_submitted` INT UNSIGNED NOT NULL , `user_id_drafted` INT UNSIGNED NOT NULL , PRIMARY KEY ( `vote_election_id` ) ); CREATE TABLE `users` ( `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `fname` CHAR(30) NOT NULL , 'lname' CHAR(30) NOT NULL , PRIMARY KEY ( `user_id` ) ); VOTE_ELECTION's 'user_id_submitted' and 'user_id_drafted' contain index values for 'user_id' in the second table called USERS. I would like translate the user_id_X's into the actual user's name. Is it possible to get both the submitter and drafter's names in one query? Here is what is in the table vote_elections: +------------------+-------------------+-----------------+ | vote_election_id | user_id_submitted | user_id_drafted | +------------------+-------------------+-----------------+ | 3 | 1 | 1 | | 2 | 1 | 10 | +------------------+-------------------+-----------------+ 2 rows in set (0.13 sec) Here is what is in the table for users: +----+-------+----------+ | id | fname | lname | +----+-------+----------+ | 1 | Micah | Bushouse | | 10 | Mark | Goleski | +----+-------+----------+ 2 rows in set (0.08 sec) My goal for this query is to receive a result table like this: vote_election_id submitter drafter 2 Micah Bushouse Mark Goleski 3 Micah Bushouse Micah Bushouse Here is my crap query. It blows. This is my problem. Is there a method of directing the WHERE clause to a specific result column instead to the whole result table? SELECT vote_elections.vote_election_id ,CONCAT( users.fname, SPACE(1), users.lname ) AS name FROM vote_elections ,users WHERE vote_elections.user_id_submitted = users.user_id OR vote_elections.user_id_drafted = users.user_id As you can see, it causes two separate result rows to be returned. +------------------+----------------+ | vote_election_id | name | +------------------+----------------+ | 2 | Micah Bushouse | | 2 | Mark Goleski | | 3 | Micah Bushouse | +------------------+----------------+ 3 rows in set (0.67 sec) I'm sorry for the winded problem and I'd like to sincerely thank whoever responds in advance. ~Micah -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
