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
