CODEWITHSUNDEEP
sqlsql-server
Ivan
Ivan

Reputation: 16908

SQL Select only rows where foreign key has an attribute

I have this table:

| id | Reader id | Book id | Taken date | Return date |

And, for example, 3 rows

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?

Upvotes: 2

Views: 646

Answers (3)

Sankar
Sankar

Reputation: 7107

Try this...

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

Upvotes: 1

sagi
sagi

Reputation: 40481

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

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

First identify the Reader_id who has to return books 

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)

Upvotes: 2

Related Questions