CODEWITHSUNDEEP
postgresql
khajlk
khajlk

Reputation: 861

Unable to filter rows of one table based on data in another table in PostgreSQL

I have two tables tbl1 (552 rows) and tbl2 (257 rows) with unequal rows in my PostgreSQL database (PG 9.5, x64 Windows). The tables tbl1 and tbl2 contain the following data (sample shown here):

Grp   id   val1   val2   sum
1     1    112    142    5.2
1     2    124    137    6.7
1     3    136    189    6.8
1     4    112    114    9.8
1     5    130    145    6.1
1     6    142    130    7.7

Grp  id    sum
1    1     5.2
1    3     6.8
1    6     7.7

For each group in table 1, I am trying to select rows where "id" in the first table is not equal to "id" of second table. For example, my expected output would be:

Grp   id    val1   val2   sum
1     2     124    137    6.7
1     4     112    114    9.8
1     5     130    145    6.1

I have tried this query so far but it doesn't work, and return empty output.

SELECT
   a.grp, a.id,
   a.val1, a.val2, a.sum
FROM tbl1 a
WHERE NOT EXISTS (
    SELECT * 
    FROM tbl2 b WHERE b.grp = a.grp AND b.id != a.id
)

Can someone help on this and explain what am I doing wrong?

Upvotes: 0

Views: 27

Answers (2)

forpas
forpas

Reputation: 164099

Since you are using NOT EXISTS the condition you need is b.id = a.id and not b.id != a.id:

...................
WHERE b.grp = a.grp AND b.id = a.id
...................

Upvotes: 1

cogitoergosum
cogitoergosum

Reputation: 661

The problem with your query is that the NOT EXISTS clause will always return false with the data you have shown in the sample (for each row in a, there is a row in b where grp is the same but id is different).

A LEFT JOIN with a IS NULL filter will do the trick:

SELECT a.grp, a.id,
    a.val1, a.val2, a.sum
FROM tbl1 a
LEFT JOIN tbl2 b ON a.grp = b.grp AND a.id = b.id
WHERE b.id IS NULL --b.id is null if there is no row in b which matches both a.grp and a.id

Upvotes: 1

Related Questions