simple query question

[cindy0904]

I need an SQL query for my asp page (Access db)
I only need to query one table (items).
The third line is not proper syntax, can anyone tell me how to write this?
Here's what I'm trying to do:

SELECT *
FROM items
WHERE sold = No AND catID = 7 or IS NOT NULL

assume this should be simple for someone that knows more than nearly none like me!! Thanks!

 

[cindy0904]

well, I got it, thanks anyway.

SELECT *
FROM items
WHERE sold = No AND catID = 7 OR catID is NOT NULL
ORDER BY itemname DESC

 

[karluk]

You have fixed the syntax, but I'm not convinced your query will give you the results you want. It seems to me that the clause "OR catID is NOT NULL" will give you all the rows in the table except for Null catIDs. That makes the other parts of the where clause superfluous.

Can you describe exactly what rows you want to select from your table?

 

[cindy0904]

Thanks for your reply. Yep, I'm jumping ahead of myself. You're right. My db didn't have all my items and I thought it was working but it's working just as you suspected.

My Access db has only two tables, categories and items.
both contain the field catID.
This is a used car/salvage website.
I want to display on one page the vehicles (catID 7)
and one page all others BESIDES catID 7.
I have a field "sold" which is a Yes/No field.

So, on one page I need a statement

SELECT *
FROM items
WHERE sold = No AND catID = 7 OR catID is NOT NULL
ORDER BY itemname DESC

and for the other page to display the same scenario but NOT equal to catID 7.

and then, for both counts, if the catID field is blank to simply not display the item. Of course here I guess it would be simpler to require that field in Access. That would make things a little simpler.

Any ideas? Make sense? Thanks in advance!

 

[cindy0904]

Thanks for your reply. Yep, I'm jumping ahead of myself. You're right. My db didn't have all my items and I thought it was working but it's working just as you suspected.

My Access db has only two tables, categories and items.
both contain the field catID.
This is a used car/salvage website.
I want to display on one page the vehicles (catID 7)
and one page all others BESIDES catID 7.
I have a field "sold" which is a Yes/No field.

So, on one page I need a statement

SELECT *
FROM items
WHERE sold = No AND catID = 7 OR catID is NOT NULL
ORDER BY itemname DESC

and for the other page to display the same scenario but NOT equal to catID 7.

and then, for both counts, if the catID field is blank to simply not display the item. Of course here I guess it would be simpler to require that field in Access. That would make things a little simpler.

SELECT *
FROM items
WHERE sold = No AND catID = '7'
ORDER BY itemname DESC

AND

SELECT *
FROM items
WHERE sold = No AND catID <> '7'
ORDER BY itemname DESC

Think this would make sense and forget the NULL part that confuses me!

Make sense? Thanks in advance!

 

[karluk]

Your first query seems correct. Including a check for catid='7' guarantees that catid can't be null. So the query is just as you've written

SELECT *
FROM items
WHERE sold = No AND catID = '7'
ORDER BY itemname DESC

The second query probably needs a check for nulls. Otherwise you may get matches on rows where sold = No and catid is null. Try the following:

SELECT *
FROM items
WHERE sold = No AND catID <> '7' and catID is not null
ORDER BY itemname DESC

 

[cindy0904]

Wonderful! That works great. Appreciate you taking the time, thanks so much.

cindy