CODEWITHSUNDEEP
sqlsql-server
Daniel Klöck
Daniel Klöck

Reputation: 21137

sql select all but nulls

I want to get values from two tables like in the following example:

Suppose we have this two tables:

TableA (with link to tableB):

Id | Id_TableB | Name
---------------------
1  |   1       |  Ralf
2  |   NULL    | Marta

TableB:

Id |  Color
---------------------
1  |  Blue
2  |  Red

I would like to get values for Color if there is a link, NULL otherwise:

Result:

Id |   Name | Color
-----------------------------
1  |    Ralf| Blue
2  |   Marta| NULL

Any ideas on how to solve this?

Upvotes: 1

Views: 167

Answers (4)

pratik garg
pratik garg

Reputation: 3342

just you have to use outer join for this....

select a.id,a.Name, b.Color from tableB as b 
   left join tableA as a on b.Id = a.id_tableB

and for this you can use in condition as well you can try below query--

select a.id,a.Name, b.Color from tableB as b,tableA as a on b.Id(+) = a.id_tableB

Upvotes: -1

JohnD
JohnD

Reputation: 14747

You need to use a LEFT OUTER JOIN, which includes rows even when there are NULLs. Something like this:

SELECT * 
FROM   TableA a
LEFT OUTER JOIN TableB b on a.ID_TableB = b.ID

More info here:

http://msdn.microsoft.com/en-us/library/ms187518.aspx

Hope this helps,

John

Upvotes: 1

reggie
reggie

Reputation: 13711

You need a left outer join . 

SELECT t1.Id, t1.Name, t2.Color 
from TableA t1 left outer join TableB t2 
on t1.Id_TableB = t2.Id;

Please check the link that I have provided above. Its a simplified tutorial

Upvotes: 5

Björn
Björn

Reputation: 29381

select
    a.Id,
    a.Name,
    b.Color
from
    a
    left outer join
        b
    on
        a.Id_TableB = b.Id
group by
    b.Id

The group by b.Id is necessary if you have a one-to-many relationship a->b.

Upvotes: 1

Related Questions