ansaurus

Question

Answer 1

A:

Use

subcat IS NULL

Besides that, I think you want to use:

lower(ARTICLE_NAME) LIKE '%hardy%'

because you use lowercase in the like string.

Peter Smit 2009-04-20 10:16:49

Hmm, that still gives an empty result set.

Joshxtothe4 2009-04-20 10:23:37

Try to remove the Like condition. If that gives still no rows, try subcat = "" (empty instead of NULL)

Peter Smit 2009-04-20 10:33:16

In the latter case you need to change the values of subcat (in the table)

Peter Smit 2009-04-20 10:33:47

subcat = "" works.., is this because the fieldtype is a varchar?

Joshxtothe4 2009-04-20 10:49:54

It's a varchar and the value is empty, not NULL (that's different)

Peter Smit 2009-04-20 11:41:00

Answer 2

A:

The query should look like this:

SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME, date_format(str_to_date(ACCESSSTARTS, '%d.%m.%Y %k:%i:%s'), '%d %m %Y' ) AS shortDate 
FROM AUCTIONS
WHERE upper(ARTICLE_NAME) LIKE '%hardy%' and subcat IS NULL
ORDER BY str_to_date(ACCESSSTARTS, '%d.%m.%Y %k:%i:%s'), article_no limit 0, 10

Notice the subcat IS NULL in the WHERE clause.

Rodrigo Guerreiro 2009-04-20 10:23:56

This still returns an empty result set

Joshxtothe4 2009-04-20 10:30:02

I have to use subcat ="". I set the fields to be null in mysql however. Is it because the fieldtype is varchar?

Joshxtothe4 2009-04-20 10:32:13

try this:SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME, date_format(str_to_date(ACCESSSTARTS, '%d.%m.%Y %k:%i:%s'), '%d %m %Y' ) AS shortDate FROM AUCTIONSWHERE ARTICLE_NAME LIKE '%hardy%' and (subcat IS NULL OR subcat = '')ORDER BY str_to_date(ACCESSSTARTS, '%d.%m.%Y %k:%i:%s'), article_no limit 0, 10

Rodrigo Guerreiro 2009-04-20 11:21:07

ansaurus

tags:

views:

answers:

returning query where field is null

related questions