“子查询中不允许联合操作"有没有一种方法可以在单个查询中解决此问题? [英] "Union Operation not allowed in sub query" is there a way to fix this in one single query?

问题描述

此查询剂量在Access 2000中不起作用

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

样品表

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

FRIENDS(ALL VARCHAR)
My_E_Mail               [e-mail] [e-mail]  [e-mail]
Friend_E_Mail           [e-mail]    [e-mail]   [e-mail]

如果(上面的查询中的?"为:[e-mail])所需的输出

Desired Output if ("?" in above query is: [e-mail])

+--------------+-----------+------------+
|Member        |E_Mail     |  Name      |
+---------------------------------------+
 Alester A   [e-mail]  Alester

上述查询中所需的if(?"输出为:[e-mail])

Desired Output if("?" in above query is: [e-mail])

+--------------+-----------+------------+
|Member        |E_Mail     |  Name      |
+---------------------------------------+
 Jude B        [e-mail]  Jude
 carl C        [e-mail]  Carl
 Jones J       [e-mail] Jones

PS的?" im传递?"的查询字符串参数是什么?我知道那很好.

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!?

谢谢.

推荐答案

在选择子查询中，返回的行数不能超过1. 在这种情况下，您必须使用CASE表达式.

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

您必须执行以下操作:

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 = ?;

通过这种方式，它将为每个朋友返回一行. 您会发现字段名称不是很好，请尝试对其进行重构. my_e_mais不太具有代表性.

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.

这篇关于“子查询中不允许联合操作"有没有一种方法可以在单个查询中解决此问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！