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.

 

[Pat Hartman]

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

There are other problems also.
1. The & is the standard Access concatenation operator. The + will work but it has different behavior with regard to null values so make sure you understand how they work and are using the correct operator.
2. iif(NULL,Friends.My_E_Mail, Friends.Friend_E_Mail) AS E_Mail, -- what is "null"? Null would probably always be false and so the Friends email would be used.

Rather than typing the SQL yourself, try using the QBE to build it. That will eliminate many syntax errors especially with joins.