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

Question

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?

Change to AND b.id = a.id

forpas
– forpas

2020-04-02 15:56:15 +00:00
Commented Apr 2, 2020 at 15:56 — forpas
– forpas, Commented Apr 2, 2020 at 15:56
Care to post as answer?

khajlk
– khajlk

2020-04-02 15:58:49 +00:00
Commented Apr 2, 2020 at 15:58 — khajlk
– khajlk, Commented Apr 2, 2020 at 15:58

forpas · Accepted Answer · 2020-04-02 16:01:32Z

1

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
...................

answered Apr 2, 2020 at 16:01

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cogitoergosum · Accepted Answer · 2020-04-02 15:54:16Z

1

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

answered Apr 2, 2020 at 15:54

cogitoergosum

7016 silver badges13 bronze badges

1 Comment

khajlk Over a year ago

Hey, thanks for your help, I tried but the trick with NULL didn't work, cheers!

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related