CODEWITHSUNDEEP
sqlpostgresqlleft-join
Javier
Javier

Reputation: 49

Joining a column from a table that has no value

I have one table, called table A, which is a list of ID's

Table A

id | 
1  |
2  |
3  |
4  |
5  |

Then I have another table called table B, which is a list of ID's and a value

Table B 

id | value
1  | 473
3  | 333
4  | 533

I want to select all the ID's from table A even if they don't have a corresponding value in table B (the non-corresponding values can be blank/null or zero), what I want is below:

id |
1  | 473
2  | 
3  | 333
4  | 533
5  |

Upvotes: 0

Views: 40

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

The simplest way is a LEFT JOIN with USING in the join condition:

SELECT *
FROM   a
LEFT   JOIN b USING (id);

Upvotes: 2

Tony Quiriconi
Tony Quiriconi

Reputation: 119

Left join will get you what you need. You can even put a value in for the value where there is none. This could be good for reports and what not. 

    select 
    a.id
    ,case when(b.value in ('Null', ' ')then 'No Value'
    else b.value
    end as Value

    from Table_A a

    left join Table_B b
    on B.id = A.id

Upvotes: 0

Todd Cunningham
Todd Cunningham

Reputation: 1

SELECT
a.ID
,b.value
FROM A as a
LEFT JOIN B as b
ON a.ID = b.ID

Using the LEFT JOIN here on B will provide you all of A while showing the known and unknown matches of B.

Upvotes: 0

Cristian Lupascu
Cristian Lupascu

Reputation: 40506

Use a simple LEFT JOIN:

select a.id, b.value
from TableA a
left join TableB b
  on a.id = b.id

Upvotes: 0

Related Questions