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

Question

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,

@ŁukaszKamiński Nope, LEFT JOIN does not solve this. I still get the same result where i only get object that have properties. Probabli UNION would be solution. 1 SELECT for object with properties and another for whidout properties — Marko Taht
– Marko Taht, Commented May 25, 2017 at 6:57
then I'd like to see the stucture, data sample, query and failing result — Vao Tsun
– Vao Tsun, Commented May 25, 2017 at 7:17

Łukasz Kamiński · Accepted Answer · 2017-05-25 07:23:38Z

1

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

answered May 25, 2017 at 7:23

Łukasz Kamiński

6,0401 gold badge26 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related