选择问题数据的ms-access

i have data that looks like this:

我的数据是这样的:

alex hub
liza fds
harry ok
lena yyy
liza ok

i would like a sql statement that will give me this:

我想要一个sql语句，它会给我以下信息:

alex hub
lena yyy

i need to return all the column1's where there is no correspending "ok" for column2

我需要返回所有的column1，那里没有与column2相关的“ok”

please note that if there is just ONE "ok" that is enough to not return that data

请注意，如果只有一个“ok”就足以不返回该数据

thanks so much for your help

非常感谢你的帮助

3 个解决方案

#1

Try this sql query:

试试这个sql查询:

select Col1, Col2 from MyTable
where Col1 not in (
    select Col1 
    from MyTable 
    where Col2 = 'ok'
)

#2

The LEFT JOIN to a subquery of "ok" rows and the WHERE (((t2.column1) Is Null)) return only those column1 values which have no row with "ok" as column2.

“ok”行的子查询的左连接和WHERE ((t2.column1)为空)只返回那些没有行“ok”为column2的column1值。

SELECT t1.column1, t1.column2
FROM Table1 AS t1
    LEFT JOIN [
        SELECT column1, column2
        FROM Table1 WHERE column2 = "ok"]. AS t2
    ON t1.column1 = t2.column1
WHERE (((t2.column1) Is Null));

#3

Seems to me that the most straightforward way to do this is simply:

在我看来，最直接的方法就是:

SELECT people.personname, people.status
FROM people
WHERE [people]![status]<>'ok';

Sometimes it is useful to go through the exercise of implementing your answer in Access. I discovered that I can't use name as a columname, and there is a third record that meets the condition.

有时，在Access中执行您的答案是有用的。我发现我不能使用名称作为一个columname，并且有第三个记录满足条件。

personname  status
==========  ======
alex        hub
liza        fds
lena        yyy

#1