7

I have a two tables,Transaction and Master. The transaction table shows a from and to activity id. The master shows the activity id and a name. The from and to id use the same master table to associate the activity id and name. What I would like to do is take the distinct from and to values and display them with the associated name.

The original query was 

select distinct a.from, a.to from Transaction a

What I need is something where a.from is followed by b.name and a.to is followed by b.name

I know that I have to have a join but I need the join to apply to each of the distinct a.values.

In concept I would like to do 2 joins with one on each of the a.values but I am not sure how to delineate the from and to values.

Tim Vavra
  • 537
  • 1
  • 19
  • 35
  • 1
    Join the Master table twice. For example: http://stackoverflow.com/a/4267979/12601 – Greg Aug 05 '13 at 19:51
  • Can you add a simple example with, say, 3 columns from each table and an example of the output you want from the query? – user1676075 Aug 05 '13 at 19:52

1 Answers1

9
select distinct a.from, f.name as FromName, a.to, t.name as ToName 
from Transaction a
join Master F on a.from = f.id
join Master T on a.to = f.id
HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • This is what Greg suggested also. By giving the same table two aliases I am able to join the two fields independently of each other. Thanks to everyone who helped. – Tim Vavra Aug 06 '13 at 13:17