CODEWITHSUNDEEP
sqlpostgresql
user2156353
user2156353

Reputation: 45

Exclude a group when one row match in another table

I have two tables :

the first one called "card" with one column "id".

| id |
| 1  |
| 2  |
| 3  |
| .. |

The second table is named "waste" with two columns "card_id" and "waste_type".

| card_id | waste_type |
|   1     |     1      |
|   1     |     3      |
|   2     |     2      |
|   2     |     1      |

And i want to select only the card where there is no waste_type = 2

The query should look like this :

SELECT c.id FROM card c
JOIN waste w
ON c.id = w.card_id
WHERE waste_type <> 2

I want this result :

id
1

But i get :

id
1
2

How can i do that ? Thank you so much in advance !

Upvotes: 2

Views: 70

Answers (1)

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60503

You should use a not exists clause for that.

select c.id
from card c
where not exists (select null from waste w
                  where w.card_id = c.id
                  and w.waste_type = 2)

With your query, I would guess you rather retrieve

1
1
2

Upvotes: 2

Related Questions