检索所有元组，除了一个带有子查询的元组

I want to retrieve all the users with the same country name, but except the user, I am comparing with other. this query gives me an error saying that the sub query returns more than one row. anyone can help me in this regard, please.

我想检索具有相同国家名称的所有用户，但是除了用户之外，我正在与其他用户进行比较。这个查询给我一个错误，说子查询返回不止一行。在这方面谁都可以帮助我。

SELECT * FROM users u, address a WHERE a.email=u.email AND a.country LIKE (SELECT country FROM address WHERE NOT (email = '[e-mail]'));

3 个解决方案

#1

select *
from users u, address a
where a.email=u.email
and a.country = (select country from address where email = '[e-mail]')
and u.email <> '[e-mail]'

OR

或

select *
from users u, address a,
(select country from address where email = '[e-mail]') c
where a.email=u.email
and a.country = c.country
and u.email <> '[e-mail]'

#2

I didn't understand your question before. Try this,

我以前不明白你的问题。试试这个,

SELECT * FROM users s
JOIN address a on a.email = u.email 
WHERE a.country in(SELECT country from address where email = '[e-mail]')
AND a.email != '[e-mail]'

#3

try this one:

试试这个:

SELECT * FROM users u, address a 
  WHERE a.email=u.email 
  AND u.email != '[e-mail]'
  AND a.country IN (SELECT country FROM address WHERE email ='[e-mail]');