CODEWITHSUNDEEP
postgresql
Marko Taht
Marko Taht

Reputation: 1522

PostgreSQL join 2 tables and get all values even if 1 table is empty

I have 2 table. Objects and properties. Properties table has properties of the object. But it is possible that the object does not have any properties. I would like to make a query so that I get all the objects that have properties(value in property column) and all the objects that dont have properties(in this case the property column will be empty)

EXAMPLE: Simplified query that gives the same result

SELECT 
row_number () OVER() AS id,
seire.id seire_id,
tegevus.arenguvajadus
FROM andmed seire
RIGHT OUTER JOIN tegevused tegevus ON  seire.id = tegevus.seire_id
WHERE tegevus.aktiivne = true

Data example:

andmed:

Id, Data

1 , ...
2, ...

tegevused

id, aktiivne, arenguvajadus, seire_id

1, true, something something, 1

1, true, something2 , 1

Expected result

  ID, Seire_id, arenguvajadus

    1, 1, something something

    2, 1, something2

    3, 2,

Upvotes: 1

Views: 240

Answers (1)

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

You need to remove that LEFT JOINed table from your WHERE. I assume tegevused is properties.

SELECT 
row_number () OVER() AS id,
seire.id seire_id,
tegevus.arenguvajadus
FROM andmed seire
LEFT OUTER JOIN tegevused tegevus ON  seire.id = tegevus.seire_id AND tegevus.aktiivne = true

Upvotes: 1

Related Questions