I have two tables messages and members.
id | from_member_id | to_member_id | date | subject | body
1 | 1 | 2 | 2005-03-30 05:05:055 | test | testing it
id | name
1 | sukh
2 | dukh
Now messages.from_member_id and messages.to_member_id are foreing keys pointing to same primary key column member.id
I want to know is it possible to get names of both members using one join.
following query cannot work I think as how dbms will know which member name is from and which is to.
select member.name as from_member, member.name as to_member, messages.date, messages.subject from members, messages where members.id=messages.from_member_id