MS Access/JET "Join Expression Not Supported" Any Way to Fix This Query?

[jesusw00t]

MY CODE (Im using MS Access 2000 with JET Database engine)

SELECT Members.First_Name + ' ' + Members.Last_Name AS Member,
iif(NULL,Friends.My_E_Mail, Friends.Friend_E_Mail) AS E_Mail,
Members.First_Name AS Name
FROM ((Members
LEFT OUTER JOIN Friends
ON Members.E_Mail = Friends.My_E_Mail
AND Friends.Friend_E_Mail = ?)
LEFT OUTER JOIN Friends Friends_1
ON Members.E_Mail = Friends.Friend_E_Mail
AND Friends.My_E_Mail = ?)

My Tables
Members(all VARCHAR) SOME DATA
First_Name Alester Jude Carl Jones
Last_Name A B C J

FRIENDS(ALL VARCHAR)
My_E_Mail Alester"AT" lam.com Alester"AT" lam.com Alester"AT" lam.com
Friend_E_Mail jude"AT" lam.com carl"AT" lam.com jones"AT" lam.com

***NOTE Friends Table ALLOWS duplicates so jude can be on my_E_Mail but CAN NOT add alester because they are already friends.


Desired Output if ("?" in above query is: jones"AT" lam.com)

+--------------+-----------+------------+
|Member |E_Mail | Name |
+---------------------------------------+
Alester A Alester"AT" lam.com Alester




Desired Output if("?" in above query is: Alester"AT" lam.com)

+--------------+-----------+------------+
|Member |E_Mail | Name |
+---------------------------------------+
Jude B jude"AT" lam.com Jude
Carl C carl"AT" lam.com Carl
Jones J jones"AT" lam.com Jones

PS the "?" are query string parameters that im passing in the "?" i know that works fine.


MY QUESTION IS : I keep getting this error "Join Expression not Supported"

Is there a workaround query I can use without using a stored procedure or using multiple queries since this needs to be ONE SINGLE QUERY!?

 

[boblarson]

Re: MS Access/JET "Join Expression Not Supported" Any Way to Fix This Query?

It is LEFT JOIN, not LEFT OUTER JOIN.