CODEWITHSUNDEEP
postgresqlpostgresql-10
Dennis
Dennis

Reputation: 1975

How can I filter table based on another tables data having condition?

This is folders table:

+------------+
| id  name   |
|------------|
|  1  dir1   |
|  2  dir2   |
+------------+

This is files table:

+------------------------------+
| id  folder_id  name  deleted |
|------------------------------|
|  1  1          file1 true    |
|  2  1          file2 false   |
|  1  2          file3 true    |
|  2  2          file4 true    |
+------------------------------+

As you can see, dir1 and dir2 has 2 files per dir. But in dir1 only one file deleted and one file is available. However, dir2 has 2 files but both of them deleted. So no files remains in there.

What I am trying to do is get folders table and dirs that doesn't have any files (all deleted).

So far I tried this but did not worked.

SELECT
    "dirs".* 
FROM
    "dirs"
    INNER JOIN "files" ON ( "files"."folder_id" = "folders"."id" ) 
WHERE
    ( "files"."deleted" IS FALSE ) 
GROUP BY
    "folders".ID 
HAVING
    COUNT ( files.ID ) != 0 /* or > 0 or == 0 */

Expected result:

+------------+
| id  name   |
|------------|
|  2  dir2   |
+------------+

Because dir2 has files but all deleted.

Upvotes: 1

Views: 58

Answers (1)

klin
klin

Reputation: 121534

Use where not exists:

select *
from folders d
where not exists (
    select 1
    from files f
    where folder_id = d.id
    and not deleted)

Working example in rextester.

Upvotes: 2

Related Questions