SQL-返回所有行中至少有一个值为“ Y”的行 [英] SQL - Return all Rows Where at Least one has value 'Y'

问题描述

我的问题与以前的帖子非常相似
SQL查询，如果另一列等于x

My question is very similar to this previous post SQL Query, get a columns if another columns equal to x

，则获得一列，唯一的区别是我要联接两个表和先前的解决方案似乎不起作用。基本上，一旦连接了表，我就有两个列。我想要一个名称的所有行，其中该名称的至少一行具有 Shasta作为位置。例如，

The only difference is I am joining two tables and the previous solution does not seem to work. Basically I have two Columns once the tables have been joined. I want all rows of a name, where at least one row for that name has "Shasta" as a location. For example,

第1列=名称（来自表1）
第2列=位置（来自表2）

Column 1 = Name (From Table 1) Column 2 = Location (From Table 2)

Name   |  Location
-------------------
Bob    |   Shasta
Bob    |   Leaves
Sean   |   Leaves
Dylan  |   Shasta
Dylan  |   Redwood
Dylan  |   Leaves

应该返回：

Name   |   Location
--------------------
Bob    |   Shasta
Bob    |   Leaves
Dylan  |   Shasta
Dylan  |   Redwood
Dylan  |   Leaves

我尝试了上一篇文章的解决方法

I tried the solution of the previous post

where x in
(
  select distinct x
  from table 1
  where y like 'Shasta'
)

不幸的是，它仅返回：

Name   |  Location
--------------------
Bob    |   Shasta
Dylan  |   Shasta

推荐答案

只需使用 EXISTS 如果存在具有相同名称和位置的行，则返回一行Shasta：

Simply use EXISTS to return a row if there exists a row with same name and location Shasta:

select name, location
from tablename t1
where exists (select 1 from tablename t2
              where t1.name = t2.name
                and t2.locaion = 'Shasta')

这篇关于SQL-返回所有行中至少有一个值为“ Y”的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！