How to Display the NULL

[gio2888]

heres a SQL statement...
SELECT * FROM portal_main P, portal_cat_title C WHERE P.category = C.category AND ( ( name like '%fyi%' ) OR ( portal_keywords like '%fyi%' ) ) AND ((P.unit_id = '') OR (unit_id like '%NEB%')) OR (unit_id like '%NULL%') ORDER BY name

I want the results to show all the ones with unit_id being blank, NEB, and NULL. I couldn t get it to show the NULLs, I got the blank ones and the NEB ones. Thanks in advance!

 

[skicamel]

Try changing that last bit to this:

((P.unit_id = '') OR (unit_id like '%NEB%') OR (unit_id is NULL))

Note I also moved the closing parenthesis...Looks like that's what you were going for.

 

[gio2888]

It didn t work, only the ones with "NEB" and blanks showed up.

 

[jester777]

skicamel is right.
You have to use special syntax to deal with NULL values. In this case it is the 'IS NULL' syntax.
You can test this on your table just by doing
select * from portal_main where unit_id is null
This should return all the rows where unit_id has a NULL value.

 

[skicamel]

I may have made the wrong assumption w/ moving the parenthesis. This has your original grouping w/ the new syntax. Hope this works for you.

SELECT * FROM portal_main P, portal_cat_title C
WHERE P.category = C.category
AND
(( name like '%fyi%' ) OR ( portal_keywords like '%fyi%' ))
AND
((P.unit_id = '') OR (unit_id like '%NEB%'))
OR
(unit_id is NULL)
ORDER BY name

 

[gio2888]

thanks for the help!