Page 1 of 1

WHERE col = NULL ?

Posted: Thu Jun 08, 2006 4:08 am
by jurriemcflurrie
Weirdan | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I have a small query problem. First, these are my tables:

[syntax="sql"]CREATE TABLE `profile_fields` (
`field` VARCHAR( 255 ) NOT NULL ,
`type` VARCHAR( 255 ) NOT NULL ,
`size` INT( 3 ) NOT NULL ,
`max` INT( 5 ) NOT NULL ,
UNIQUE (
`field`
)
) ENGINE = MYISAM ;

CREATE TABLE `profile_data` (
`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`user` VARCHAR( 32 ) NOT NULL ,
`field` VARCHAR( 255 ) NOT NULL ,
`value` TEXT NOT NULL ,
`timestamp` INT( 10 ) NOT NULL ,
INDEX ( `user` , `field` )
) ENGINE = MYISAM ;

What I want to do is select all fields, and join the users data to it. But, when I do a WHERE on the user column, I only get results of fields that are already filled in, and that's not what I want. I want all fields, and if there's userdata, it should be there.

This is what I planned to do:

Code: Select all

SELECT *
FROM profile_fields
LEFT JOIN profile_data ON profile_fields.name = profile_data.field
WHERE profile_data.user = '$user'
With this query, I only get results where a user matches (so I'm missing some field data). I thought I'd do this:

Code: Select all

SELECT *
FROM profile_fields
LEFT JOIN profile_data ON profile_fields.name = profile_data.field
WHERE
    profile_data.user = '$user' OR
    profile_data.user = NULL
but that doesn't work neither.

Can anyone help me out?

Thanks in advance!


Weirdan | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Thu Jun 08, 2006 5:29 am
by x
Weirdan | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


im not an expert on this but i know left join should connect even if theres null, unlike inner join. could you try reversing the order of the two tables in the join or use right join?

[syntax="sql"]
SELECT *
FROM profile_data
LEFT JOIN profile_fields ON profile_fields.name = profile_data.field
WHERE profile_data.user = '$user'
or

Code: Select all

SELECT *
FROM profile_fields
RIGHT JOIN profile_data ON profile_fields.name = profile_data.field
WHERE profile_data.user = '$user'

Weirdan | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Thu Jun 08, 2006 6:04 am
by jurriemcflurrie
No luck.

It seems that WHERE user = '$user' limits the result to exactly match the where clause. That's why I tried to do a AND user = NULL, but that won't work also..

Posted: Thu Jun 08, 2006 6:22 am
by paladaxar
OK...nothing ever "equals" NULL someting either IS NULL or IS NOT NULL. Try "WHER col IS NULL"

Posted: Thu Jun 08, 2006 6:27 am
by jurriemcflurrie
Ok, solved!!

paladaxar, your suggestion didn't work. I still got no NULL results, and I did get results from other users :)



The query should be as follows:

Code: Select all

SELECT *
FROM profile_fields
LEFT JOIN profile_data ON 
	profile_fields.name = profile_data.field AND
	profile_data.user = '$user'

Thanks anyway, maybe we saved someone else a lot of time ;)

Posted: Thu Jun 08, 2006 6:33 am
by paladaxar
Hey, I'm glad you got it.

Do remember though, never to say "equals NULL".

That trips up a lot of people.

Posted: Thu Jun 08, 2006 6:36 am
by jurriemcflurrie
And why is that exactly?

I can equal false, '' and 0

so why not NULL

Posted: Thu Jun 08, 2006 6:58 am
by paladaxar
NULL is an interesting little guy. It really means "non existent". Nothing can equal non existent. Something can exist, or it IS non existent.

You should only use NULL for something that doesnt yet exist. Never in place of a known zero value. Like the number of legs on an apple is 0. It is not NULL because we know that an apple has no legs. (I know...it was a stupid example...but it works)

If we have a package that is scheduled to ship sometime next week, the 'shipped_out_date' columb will be NULL. The package may have an anticipated ship date, but since it has not yet actually shipped, it's actual ship date is NULL. Also, we wouldnt want to use 0 here because that could confuse our server. If we are looking for all packages shipped before June 1, 2006, we would get this package in our results.

Doing a quick google search, I came up with this page:

http://www.sqlservercentral.com/columni ... ersion.asp

about half way down the page it directs you here:

http://www.sqlservercentral.com/columni ... isnull.asp

I didnt read the article since it wanted me to create an account. But, if you already have an account, it looks like an article that will explain exactly what we are talking about.

Try a few google searches on it. It is something that has tripped up SQL coders for years (experienced and unexperienced). Once you understand it, it shouldnt give you any more problems. It's getting the initial understanding of the subject that can be tough. It is, indeed, unintuitive at first.

Posted: Thu Jun 08, 2006 7:02 am
by paladaxar
Just found another good site on this topic:

http://www.xaprb.com/blog/2006/05/18/wh ... ng-in-sql/

Hope that helps.

Posted: Fri Jun 09, 2006 12:50 am
by jurriemcflurrie
Ok thanks, I'll have a read.

But I think I get it already!