Problem when field is NULL

I am doing a LEFT JOIN, that should display all files in table a even if
there is no match with table B as far as I know.
The problem is that if the field that joins A with B is NULL then I get no
results, I am trying the following:
SELECT a.Id , a.CaseId , a.EmpId, a.AlienId, a.FirmAddressId, b.FirstNm ,
b.MiddleNm, b.LastNm , b.FirmId, c.Processcatalog , a.MainCase ,
d.MaidenNm
FROM Cases a LEFT JOIN Users as d ON a.EmpId = d.UserId
LEFT JOIN Users as b ON ISNULL (a.AlienId,'0') = b.UserId
INNER JOIN Processcatalog as c ON Process = ProcesscatalogID WHERE
ISNULL (a.CaseID,'') LIKE '%MMColParam%' AND ISNULL (b.LastNm,'') LIKE
'MMColParam1%' AND ISNULL (b.FirstNm,'') LIKE 'MMColParam2%' AND
b.Firmid = 'MMColParam3' AND a.archived LIKE 'MMColParam4' and
a.firmaddressid LIKE 'MMColParam5' and ISNULL(a.firmaddressid,'')LIKE
'MMColParam5' AND ISNULL(d.UserID,'') LIKE 'MMColParam6' AND MainCase IS
NOT NULL
ORDER BY a.caseId
---
a = Cases (This is the main table)
b = Contacts (This is the table I am joining to using LEFT join)
Even though I do it like this and I give value = 0 when it is NULL I still
get no matches, and there is at least one results where there is one record
for 'cases' where cases.alienid is NULL, still won't show up.
What am I missing ?
Let me know if you need more info to help, thanks !
AleksOn Fri, 11 Feb 2005 16:00:50 -0500, Aleks wrote:

>I am doing a LEFT JOIN, that should display all files in table a even if
>there is no match with table B as far as I know.
>The problem is that if the field that joins A with B is NULL then I get no
>results, I am trying the following:
>--
>SELECT a.Id , a.CaseId , a.EmpId, a.AlienId, a.FirmAddressId, b.FirstNm ,
>b.MiddleNm, b.LastNm , b.FirmId, c.Processcatalog , a.MainCase ,
>d.MaidenNm
>FROM Cases a LEFT JOIN Users as d ON a.EmpId = d.UserId
>LEFT JOIN Users as b ON ISNULL (a.AlienId,'0') = b.UserId
>INNER JOIN Processcatalog as c ON Process = ProcesscatalogID WHERE
>ISNULL (a.CaseID,'') LIKE '%MMColParam%' AND ISNULL (b.LastNm,'') LIKE
>'MMColParam1%' AND ISNULL (b.FirstNm,'') LIKE 'MMColParam2%' AND
>b.Firmid = 'MMColParam3' AND a.archived LIKE 'MMColParam4' and
>a.firmaddressid LIKE 'MMColParam5' and ISNULL(a.firmaddressid,'')LIKE
>'MMColParam5' AND ISNULL(d.UserID,'') LIKE 'MMColParam6' AND MainCase IS
>NOT NULL
>ORDER BY a.caseId
>---
>a = Cases (This is the main table)
>b = Contacts (This is the table I am joining to using LEFT join)
>Even though I do it like this and I give value = 0 when it is NULL I still
>get no matches, and there is at least one results where there is one record
>for 'cases' where cases.alienid is NULL, still won't show up.
>What am I missing ?
Hi Aleks,
All filtering on the outer-join-ed tables should be move to the ON clause.
To explain: if a row from a (Cases) has no match in b (Users), then the
value of b.FirstNm will be NULL (becuase of the outer join). In the WHERE
clause, you have "... AND ISNULL (b.FirstNm,'') LIKE 'MMColParam2%'", so
these outer-joined rows will fail this test and be removed from the result
set.
You also don't need to use ISNULL here - NULL LIKE 'MMColParam2%' will not
evaluate to true anyway.
Finally, it's better to use = instead of LIKE when searching for one
specific value. Use LIKE only when searching for patterns.
SELECT a.Id, a.CaseId, a.EmpId, a.AlienId, a.FirmAddressId,
b.FirstNm, b.MiddleNm, b.LastNm, b.FirmId,
c.Processcatalog, a.MainCase, d.MaidenNm
FROM Cases AS a
LEFT JOIN Users AS d
ON d.UserId = a.EmpId
AND d.UserID = 'MMColParam6'
LEFT JOIN Users AS b
ON a.AlienId = b.UserId
AND b.LastNm LIKE 'MMColParam1%'
AND b.FirstNm LIKE 'MMColParam2%'
AND b.Firmid = 'MMColParam3'
INNER JOIN Processcatalog AS c
ON ?.Process = ?.ProcesscatalogID -- better qualify ALL columns
WHERE a.CaseID LIKE '%MMColParam%'
AND a.archived = 'MMColParam4'
AND a.firmaddressid = 'MMColParam5'
AND ?.MainCase IS NOT NULL -- better qualify ALL columns
ORDER BY a.caseId
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I am using LIKE because people search for names using the first letters, not
all the name, that gives them flexibility, I use ISNULL because sometimes
they leave the last name of the first name blank, which is NULL in the
database, but I still need to return that record.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:bg9q01dk2cr489b62eqtkbq7093gujnv9q@.
4ax.com...
> On Fri, 11 Feb 2005 16:00:50 -0500, Aleks wrote:
>
> Hi Aleks,
> All filtering on the outer-join-ed tables should be move to the ON clause.
> To explain: if a row from a (Cases) has no match in b (Users), then the
> value of b.FirstNm will be NULL (becuase of the outer join). In the WHERE
> clause, you have "... AND ISNULL (b.FirstNm,'') LIKE 'MMColParam2%'", so
> these outer-joined rows will fail this test and be removed from the result
> set.
> You also don't need to use ISNULL here - NULL LIKE 'MMColParam2%' will not
> evaluate to true anyway.
> Finally, it's better to use = instead of LIKE when searching for one
> specific value. Use LIKE only when searching for patterns.
> SELECT a.Id, a.CaseId, a.EmpId, a.AlienId, a.FirmAddressId,
> b.FirstNm, b.MiddleNm, b.LastNm, b.FirmId,
> c.Processcatalog, a.MainCase, d.MaidenNm
> FROM Cases AS a
> LEFT JOIN Users AS d
> ON d.UserId = a.EmpId
> AND d.UserID = 'MMColParam6'
> LEFT JOIN Users AS b
> ON a.AlienId = b.UserId
> AND b.LastNm LIKE 'MMColParam1%'
> AND b.FirstNm LIKE 'MMColParam2%'
> AND b.Firmid = 'MMColParam3'
> INNER JOIN Processcatalog AS c
> ON ?.Process = ?.ProcesscatalogID -- better qualify ALL columns
> WHERE a.CaseID LIKE '%MMColParam%'
> AND a.archived = 'MMColParam4'
> AND a.firmaddressid = 'MMColParam5'
> AND ?.MainCase IS NOT NULL -- better qualify ALL columns
> ORDER BY a.caseId
> (untested)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 11 Feb 2005 16:51:19 -0500, Aleks wrote:

>I am using LIKE because people search for names using the first letters, no
t
>all the name, that gives them flexibility,
Hi Aleks,
Your code didn't allow for user input. It was just
somecol LIKE 'MMColParam2%' (where LIKE is fine) or
somecol LIKE 'MMColParam4' (where no wildcard is used, so LIKE has no
added value; use = to get better performance)

> I use ISNULL because sometimes
>they leave the last name of the first name blank, which is NULL in the
>database, but I still need to return that record.
But if you use ISNULL (a.AlienId,'0') = b.UserId, you'll only return the
row if b.UserID happens to be equal to '0', which is not too likely to
happen. And if you use ISNULL (b.FirstNm,'') LIKE 'MMColParam2%', you'll
only return the row when '' is LIKE 'MMColParam2%', which will definitely
never be the case, so the row won't be returned.
Q1: Did you try the query I suggested? Did it work?
Q2: Since I now have a feeling that your real problem is quite different
from the simplification you posted, could you now post something a little
bit closer to the real issue? If possible with SQL to construct your
tables and fill them with some sample data, and with a description of the
requested output from that sample data. See www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)