"Union Operation not allowed in sub query" is there a way to fix this in one single query?

Question

This Query Dose NOT work in Access 2000

SELECT (Members.First_Name + " " + Members.Last_Name)AS Member,
(SELECT Friend_E_Mail,
FROM    Friends,Members WHERE My_E_Mail = ? and Friend_E_Mail <> ?
UNION ALL
SELECT My_E_Mail,FROM  Friends,Members 
WHERE  Friend_E_Mail = ?and My_E_Mail <> ?) AS  E_Mail ,
(Members.First_Name) AS Name

FROM Members,Friends

Sample 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@lam.com Alester@lam.com  Alester@lam.com
Friend_E_Mail           jude@lam.com    carl@lam.com   jones@lam.com

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

+--------------+-----------+------------+
|Member        |E_Mail     |  Name      |
+---------------------------------------+
 Alester A   Alester@lam.com  Alester

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

+--------------+-----------+------------+
|Member        |E_Mail     |  Name      |
+---------------------------------------+
 Jude B        jude@lam.com  Jude
 carl C        carl@lam.com  Carl
 Jones J       jones@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 "Operation Not Allowed in Sub Query"

is their a work around query i can use without using a stored procedure or using multiple queries since this needs to be ONE SINGLE QUERY!?

Thanks.

Why are you including Members in the UNION queries when both My_E_Mail and Friend_E_Mail are in the Friends table? — ron tornambe, May 14 '13 at 20:47

score 0 · Accepted Answer · answered May 14 '13 at 20:31

0

You can´t return more then 1 row in a select subquery. To this case you have to use the CASE expression.

You have to do something like this:

SELECT (m.First_Name + " " + m.Last_Name) AS Member,
       case 
       when f1.my_email is null 
        then f2.my_e_mail
        else f1.friend_email 
       end as email,
       m.First_Name AS Name
from members m
left outer join friends f1
  on m.email = f1.my_e_mail
  and f1.friend_e_mail = ?
left outer join friends f2
  on m.email = f2.friend_e_mail
  and f2.my_e_mail = ?;

Thys way it will return a row for each friend. You can see that the names of the fields are not so good, try to refactory it. my_e_mais isn´t so representative.

answered May 14 '13 at 20:31

Rodrigo

188
1
8

@Rodigo 'SELECT (Members.First_Name + ' ' + Members.Last_Name) AS Member, (CASE WHEN Friends.My_E_Mail IS NULL THEN Friends.My_E_Mail ELSE Friends.Friend_E_Mail END) 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 ON Members.E_Mail = Friends.Friend_E_Mail AND Friends.My_E_Mail = ?;' – James Putin May 15 '13 at 15:42
oh never mind CASE dose not work in Access IIF() dose Thank you very much – James Putin May 15 '13 at 15:57
1 - You have to put diferent aliases on your friends table. Look my example. 2 - You can just change the case for IIF. If it works, please acept the answer, thanks. – Rodrigo May 15 '13 at 17:47
You have to use like this: iif(f1.my_email is null, f2.my_e_mail, f1.friend_email) Look the aliases, f1 and f2. – Rodrigo May 15 '13 at 17:51
See this link: http://www.sitepoint.com/forums/showthread.php?440182-Join-expression-not-supported . Access supports JOINs just fine (as long as you remember to parenthesize them) – Rodrigo May 15 '13 at 20:33

"Union Operation not allowed in sub query" is there a way to fix this in one single query?

1 Answers1

Linked