CODEWITHSUNDEEP
sql
dropWizard
dropWizard

Reputation: 3538

SQL - filtering by children (or foreign key)

I am using SQLite

Assume I have 2 tables:

Table 1 - person

| id | username | email          |
| 1  | jdoe     | [email protected]   | 
| 2  | jane     | [email protected] |


Table 2 - survey

| id | answered_survey | date       | person.id |
| 1  | no              | 01/01/2019 | 1         |
| 2  | yes             | 01/05/2019 | 1         |
| 3  | no              | 01/06/2019 | 2         |

I'd like to do something like: Show me every person that does not have any children with yes in the answered_survey column.

I'm stuck with trying to query by "person does not have any children with X value in Y column", I just keep getting "show me any person that has a child with no in answered_survey

Upvotes: 0

Views: 19

Answers (2)

Dario
Dario

Reputation: 2713

The easiest way to solve your problem is:

SELECT * 
  FROM person 
 WHERE id NOT IN (
    SELECT DISTINCT personid
      FROM survey 
     WHERE answered_survey = 'yes'
);

If you need only the id’s, 

SELECT id FROM person 
EXCEPT 
SELECT DISTINCT personid FROM survey WHERE answered_survey = 'yes';

is even simpler, but the two SELECT’s must return comparable records (same number of fields with same types), hence we must restrict to id and personid.

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

One option with not exists.

select *
from person p
where not exists (select 1 
                  from survey s 
                  where p.id = s.personid
                  and s.answered_survey = 'yes'
                 )

Upvotes: 3

Related Questions