[Resolved] SQL help

[JoeBrewer]

[FONT=courier new]Hi

I'm trying to write a simple messaging system using the following table designs (they have been simplified to aid

explanation).

Users table:

| ID | Name |
|-----+--------+
| 1 | Joe |
| 2 | Andy |
| 3 | Liz |
| 4 | Jen |

Messages table:

| Mail ID | Date Sent | To | From | Subject |
+---------+-----------+----+------+------------------------------------+
| 1 | 20040601 | 1 | 3 | Hello
| 2 | 20040602 | 3 | 1 | Re: Hello
| 3 | 20040602 | 4 | 3 | Have just spoken to Joe!

How do I write a query that produces the following data:

| Mail ID | Date Sent | ToName | FromName | Subject |
+---------+-----------+--------+----------+------------------------------------+
| 1 | 20040601 | Joe | Liz | Hello
| 2 | 20040602 | Liz | Joe | Re: Hello
| 3 | 20040602 | Jen | Liz | Have just spoken to Joe!

I can get one of the names in the outputted data using an INNER JOIN, but cannot see how to get both.

Any help would be much appriciated....

Thanks,
Joe
[/FONT]

[Sxooter]

This is a basic join. You'll be mushing these tables up against each other to get the output you want.

select m.mail_id,
m.datesent,
u1.name as ToName,
u2.name as FromName,
m.subject
from messages m
join users u1
on (m.toname=u1.id)
join users u2
on (m.fromname=u2.id);

Note the use of joining against the same table twice by using an alias for it (users as u1 and u2)

You'll likely want a where clause that selects everything less than x minutes old so you don't get the whole board each time, but you get the idea.

[JoeBrewer]

Thanks very much - it worked perfectly 🙂

Joe