Select when Fk can be null - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select when Fk can be null

Author

Topic

dacow
Starting Member

5 Posts

Posted - 2010-11-29 : 15:16:02

Hi. im new to sql and to the forum, also my english is not that good :D.

My problem : im trying to list (select) users and their cities, the city is a foreign key, but can be null if is not set.

This is an example :

create user(
Id int identity,
name int not null,
cityId int,
primary key nonclustered (Id)
)

create city (
Id int identity,
name varchar(20) not null,
primary key nonclustered (Id)
)

- In table user: cityId is a foreign key from the table city.
- In table user: cityId can be null.

to list all the user and their cities, even if the city is not set.
select u.*,c.name as cityNAme from user u, city c where c.Id = u.cityId

currently lists


Id    name    cityId   cityNAme
1     peter     1          new york
3     bill         1          new york

i want to list


Id    name    cityId   cityNAme
1     peter      1          new york
2     mary       null      null
3     bill          1         new york

this can be done ? without modifyng the tables ?
(i cant modify the tables, is a system where i only have access to query selects)

thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-29 : 15:25:31

You need to use a left join. (also you should use ANSI style joins)

select 
    u.*,c.name as cityNAme 
from 
    user u
LEFT OUTER JOIN 
    city c 
    ON c.Id = u.cityId

dacow
Starting Member

5 Posts

Posted - 2010-11-29 : 16:54:21

quote:
Originally posted by Lamprey

You need to use a left join. (also you should use ANSI style joins)
select
u.*,c.name as cityNAme
from
user u
LEFT OUTER JOIN
city c
ON c.Id = u.cityId

thank you very much, worked perfect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources