Cannot get to work a sentence with NULL results

Hi, Ive the following SELECT with a WHERE clause which throws a
result with no NULLs for B.idPersonal.
How do I get all A.idPersonal=B.idPersonal though B.idPersonal=Null?
SELECT C.Descripcion as TipoCF, B.Nombre, B.Apellido, E.Descripcion as
Provincia, D.Partido, D.Localidad, D.Direccion, A.idCF,
A.ComputadoraTipo, A.Marca, A.Modelo, A.Serie, B.Nombre, B.Apellido,
A.MonitorPulgadas
FROM tblCFComputadora A, tblPersonal B, tblCFTipo C, tblCentroFisico
D, tblProvincia E
WHERE A.idcf=D.idCF and D.idTipoCF=C.idTipoCF and
D.idProvincia=E.idProvincia and A.idPersonal=B.idPersonal
Thanks in advance,
Marcelo.
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/work-senten...rm.php?p=914207and (A.idPersonal=B.idPersonal or B.idPersonal is Null)
Perayu
"techuser" <UseLinkToEmail@.www.dbforumz.com> wrote in message
news:914207_259b395c0125f92ac3ae99415898
94ed@.dbforumz.com...
> Hi, I've the following SELECT with a WHERE clause which throws a
> result with no NULLs for B.idPersonal.
> How do I get all A.idPersonal=B.idPersonal though B.idPersonal=Null?
> SELECT C.Descripcion as TipoCF, B.Nombre, B.Apellido, E.Descripcion as
> Provincia, D.Partido, D.Localidad, D.Direccion, A.idCF,
> A.ComputadoraTipo, A.Marca, A.Modelo, A.Serie, B.Nombre, B.Apellido,
> A.MonitorPulgadas
> FROM tblCFComputadora A, tblPersonal B, tblCFTipo C, tblCentroFisico
> D, tblProvincia E
> WHERE A.idcf=D.idCF and D.idTipoCF=C.idTipoCF and
> D.idProvincia=E.idProvincia and A.idPersonal=B.idPersonal
> Thanks in advance,
> Marcelo.
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/work-senten...pict265873.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=914207|||techuser wrote:
> Hi, Ive the following SELECT with a WHERE clause which throws a
> result with no NULLs for B.idPersonal.
> How do I get all A.idPersonal=B.idPersonal though B.idPersonal=Null?
> SELECT C.Descripcion as TipoCF, B.Nombre, B.Apellido, E.Descripcion as
> Provincia, D.Partido, D.Localidad, D.Direccion, A.idCF,
> A.ComputadoraTipo, A.Marca, A.Modelo, A.Serie, B.Nombre, B.Apellido,
> A.MonitorPulgadas
> FROM tblCFComputadora A, tblPersonal B, tblCFTipo C, tblCentroFisico
> D, tblProvincia E
> WHERE A.idcf=D.idCF and D.idTipoCF=C.idTipoCF and
> D.idProvincia=E.idProvincia and A.idPersonal=B.idPersonal
> Thanks in advance,
> Marcelo.
Are you saying that you are joining two tables together on a column that
can contain nulls in both tables? If that's the case, you can't. NULL is
never equal to anything, including null. However, you can use a union to
get the results you want.
create table #nulltest (col1 int)
insert into #nulltest values (1)
insert into #nulltest values (2)
insert into #nulltest values (3)
insert into #nulltest values (null)
select * from #nulltest a inner join #nulltest b on a.col1 = b.col1
col1 col1
-- --
1 1
2 2
3 3
select * from #nulltest a inner join #nulltest b on a.col1 = b.col1
UNION ALL
select * from #nulltest a inner join #nulltest b on a.col1 is null and
b.col1 is null
col1 col1
-- --
1 1
2 2
3 3
NULL NULL
drop table #nulltest
David Gugick
Quest Software
www.imceda.com
www.quest.com|||A natural way would be to use explicit joins, rather than the deprecated
"from <table_list> where" syntax, and specify inner and/or outer joins as
appropriate.
However, you could try simply adding another condition:
SELECT C.Descripcion as TipoCF, B.Nombre, B.Apellido, E.Descripcion as
Provincia, D.Partido, D.Localidad, D.Direccion, A.idCF,
A.ComputadoraTipo, A.Marca, A.Modelo, A.Serie, B.Nombre, B.Apellido,
A.MonitorPulgadas
FROM tblCFComputadora A, tblPersonal B, tblCFTipo C, tblCentroFisico
D, tblProvincia E
WHERE A.idcf=D.idCF and D.idTipoCF=C.idTipoCF and
D.idProvincia=E.idProvincia and (A.idPersonal=B.idPersonal or B.idPersonal
is null)
Test, re-test, double-test, and then - just to make sure - test again! :)
ML