[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 625
  • Last Modified:

Question about where statement and not equal to results

Currently, all online requests are being displayed because I am using the following code:
<cfquery name="webdirectory" datasource="hr">
SELECT * FROM gradschool WHERE 0=0
order by id DESC

The documentation field contains several possibilities:  it is blank or contains one of the following terms:  delete, denied, approved.  I would like the delete requests not to show up; however when I use this code,

WHERE documentation <> 'delete'

the delete requests don't show up but neither do the empty - waiting to approved requested - which is bad.  Other then filling the empty field with a term, is there any code that will allow me to do this?

Thanks,
Deb
0
wwp_it
Asked:
wwp_it
  • 3
1 Solution
 
usachrisk1983Commented:
Your query should look like this:

select gradschool.*
   from gradschool
  where gradschool.documentation <> 'delete' or gradschool.documentation is null
0
 
trailblazzyr55Commented:
you may want to try one of two things depending on your database and sql.

<cfquery name="webdirectory" datasource="hr">
SELECT * FROM gradschool
WHERE documentation != 'delete'
order by id DESC
</cfquery>

or...

<cfquery name="webdirectory" datasource="hr">
SELECT * FROM gradschool
WHERE documentation <> 'delete'
order by id DESC
</cfquery>

for a single table, you don't really need to prefix with an alias, and where 0=0 is not needed unless you have a dynamic WHERE clause and some or non of the WHERE conditions will be met.
0
 
trailblazzyr55Commented:
usachrisk1983 already has demonstrated the code if you want to not show nulls as well.
0
 
trailblazzyr55Commented:
sorry misread, usachrisk1983's code will return null records in the "documentation" field, you don't really need to specify you want nulls, they will come back with everything else.
0
 
wwp_itAuthor Commented:
select gradschool.*
   from gradschool
  where gradschool.documentation <> 'delete' or gradschool.documentation is null

Chris,

Why do you use the period after gradschool in the select line?
Deb
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now