CODEWITHSUNDEEP
sqlpostgresql
GeoGyro
GeoGyro

Reputation: 559

postgresql : join with condition and without duplication

I have a table A that contains a series of unique id. I have an other table B that contains some of this id but not each one of them, a field called value and a another field called idcategory. In this table B, id can appears several times because of differents categories.

I want to list all my id in the table A in a unique way and the specific value associated in the table B in a defined categorie (idcategorie = 1). Id in the table A could not appear in the table B, but i want this information anyway in my final result and without duplication.

Here is an illustration :

Table A

id
-----
1
2
3
4
5
6
7
8

Table B

id | idcategory  | value
------------------------
1  |   1         |  red
1  |   2         |  circle
2  |   1         |  green
3  |   1         |  blue
3  |   2         |  square
4  |   1         |  green
4  |   2         |  circle 
5  |   1         |  red
5  |   2         |  square
8  |   2         |  circle

Result

id | idcategory  | value
------------------------
1  |   1         |  red
2  |   1         |  green
3  |   1         |  blue
4  |   1         |  green
5  |   1         |  red
6  |   null      |  no value
7  |   null      |  no value
8  |   null      |  no value

What is the best way to achieve this in postgreSQL ? LEFT JOIN ? UNION ?

Upvotes: 1

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You seem to want a left join:

select a.id, b.idcategory, b.value
from a left join
     b
     on b.id = a.id and b.idcategory = 1;

The value column has NULL rather than 'no value'. You can replace it, but NULL usually serves that purpose.

Upvotes: 2

Related Questions