select on two fields referencing one table

07-02-2007, 11:00 AM

I am using MySQL 5 and have two tables

users
with fields
id, name

messages
with fields
from_id, to_id

from_id and to_id references id in users

what I need is to get the name in users table for both from_id and
to_id in one SQL statement.
how can this be done?

Thanks

07-02-2007, 11:00 AM

jmark@fastermail.com wrote:
> I am using MySQL 5 and have two tables
>
> users
> with fields
> id, name
>
> messages
> with fields
> from_id, to_id
>
> from_id and to_id references id in users
>
> what I need is to get the name in users table for both from_id and
> to_id in one SQL statement.
> how can this be done?
>
> Thanks

SELECT
`u1`.`name` `from_name`,
`u2`.`name` `to_name`
FROM `messages` `m`
JOIN `users` `u1` ON `m`.`from_id` = `u1`.`id`
JOIN `users` `u2` ON `m`.`to_id` = `u2`.`id`

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
select next id in table?	Jim Michaels	Database	2	07-01-2007 9:29 PM
insert into...select using the same table	Don Vaillancourt	Database	2	07-01-2007 7:31 PM
Referencing the Table Name	Dan J.	Database	1	07-01-2007 7:06 PM
multi table select	Chuck Anderson	Database	4	07-01-2007 6:59 PM
Create a copy of a table using SELECT	Amer Neely	Database	0	06-10-2007 12:22 AM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)