Joining the same table twice to another table 1

[BasicBoy]

I have this query which I cannot get to work :

MyQuery$ = "Select

transactions.id,
transactions.transdate,
transactions.description,

votes.votename as firstvotename,
votes.votename as secondvotename

from

(((Transactions

LEFT JOIN Votes ON Transactions.paidby=Votes.voteno)
LEFT JOIN Votes ON Transactions.paidfor=Votes.voteno)

I would appreciate help, thanks

 

[r937]

you need to use aliases

SELECT transactions.id
     , transactions.transdate
     , transactions.description
     , [blue]pb_votes[/blue].votename AS paidby_votename
     , [blue]pf_votes[/blue].votename AS paidfor_votename 
  FROM (
       transactions 
INNER
  JOIN votes [blue]AS pb_votes[/blue]
    ON [blue]pb_votes[/blue].voteno = transactions.paidby
       )
INNER
  JOIN votes [blue]AS pf_votes[/blue]
    ON [blue]pf_votes[/blue].voteno = transactions.paidfor

note i made them INNER JOINs because it's unlikely that you would allow a transcation to be made for vote names which don't exist

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon