SQL只选择包含外键属性的行

I have this table:

我有这个表:

And, for example, 3 rows

例如，3行

id  Reader_id  Book_id  Taken_date  Return_date

1   1          1        1999-01-08  NULL

2   2          2        2015-03-09  2015-04-10

3   1          3        2013-01-01  2014-01-01

I need to get the id's of the readers who have returned books, so all the rows with that id in this table need to have Return_date != NULL. In the case above the query is supposed to return only the second row and not return the last row, because the reader from the last row has not returned a book. How to achieve that?

我需要获得已返回图书的读者的id，因此该表中所有具有该id的行都需要具有Return_date != NULL。在上面的例子中，查询应该只返回第二行，而不返回最后一行，因为来自最后一行的读者没有返回一本书。如何实现呢?

3 个解决方案

#1

First identify the Reader_id who has to return books

首先确定必须返回图书的Reader_id

SELECT Reader_id
FROM   yourtable
WHERE  Return_date IS NULL

Then select the readers from which is not present in above query result

然后选择上面查询结果中不存在的读取器

Use NOT IN

用不

SELECT *
FROM   yourtable
WHERE  Reader_id NOT IN (SELECT Reader_id
                         FROM   yourtable
                         WHERE  Return_date IS NULL)

Or use Not Exists which can handle NULL values from Sub-Query

或使用不存在可以处理子查询的空值。

SELECT *
FROM   yourtable a
WHERE  NOT EXISTS (SELECT 1
                   FROM   yourtable b
                   WHERE  b.Return_date IS NULL
                          AND a.Reader_id = b.Reader_id)

#2

You can do this with a left join :

你可以用左连接完成:

SELECT * FROM YourTable t
LEFT OUTER JOIN YourTable s
ON(t.reader_id = s.reader_id
   and s.id <> t.id 
   and s.Return_date is null)
WHERE s.id is null
      AND t.Return_date is not null

#3

Try this...

试试这个…

select 
      reader_id 
      from 
      tablename 
      where id not in (SELECT id
                 FROM   tablename 
                 WHERE  Return_date IS NULL)

#1