inner join and null values - 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

SQL Server 2000 Forums

SQL Server Development (2000)

inner join and null values

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2001-12-28 : 08:57:26

here is my join:

select a.closeid,
a.ordid,
a.cldate,
a.cldateend,
b.label as closer,
c.label as preparedby,
d.label as location,
e.propadd,
e.metfileid
from tblorder_close a
inner join tblresource b on a.resid=b.resid
inner join tblresource c on a.prepid=c.resid
inner join tbllocation d on a.locid=d.locid
inner join tblorder e on a.ordid=e.ordid

-----
on the second tblresource (join c) I join the prepared id
to the resource (person) in tblresource. It is not a required
field and I want to show the record even with a NULL value in the
prepid field.

can I do something like

inner join tblresource c on a.prepid=c.resid ***put a case isnull or something

thanks

slow down to move faster...

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-12-28 : 09:08:59

Hi

The way to do this is with a LEFT JOIN.

e.g.

left join tblresource c on a.prepid=c.resid

This will join where it can, and leave NULLs for the rows that it can't join. Give it a try and you will see

Damian