Jump to content


Photo

NULL stop...!


  • Please log in to reply
3 replies to this topic

#1 scrunchmuppet

scrunchmuppet
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 09 August 2006 - 07:51 PM

I'm really stuck here, come to a grinding stop just because NULL is returned in my table join. Can't get past this  :-\

I have 2 tables, 1 that holds messages and 1 that holds a record to determin if a user has viewed a message or not.
Results look like this.

+---------+-------------+
| message |  viewer  id  |
+---------+-------------+
|    1        |      NULL    |
|    2        |        5        |
|    3        |      NULL    |
|    4        |      NULL    |
|    5        |        7        |
+---------+-------------+
Record 2 has been viewed by user 5

Example;
I want to SELECT all records that DO NOT equal 'viewer id' '5' but only 1 result is being returned 'viewer id' '7', the rows with NULL values are being ignored?.

Result...
+---------+-------------+
| message |  viewer  id  |
+---------+-------------+
|    5        |        7        |
+---------+-------------+ 
No rows with NULL value are returned?

I thought records 1,3 & 4 would also be returned but this is not happening possibly because of the NULL value.

Is there way to show the records that DO NOT match and return like below

+---------+-------------+
| message |  viewer  id  |
+---------+-------------+
|    1        |      NULL    |
|    3        |      NULL    |
|    4        |      NULL    |
|    5        |        7        |
+---------+-------------+   

Or am I approaching this wrong and there is a better way to do this?

Thanks for your help...

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 August 2006 - 10:49 PM

I read this post 3 times... i don't understand.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 scrunchmuppet

scrunchmuppet
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 09 August 2006 - 11:23 PM

I think i've sorted it now,

It is a bit confusing but basically I wanted to compare table 1 against against table 2 and return fields that did not have a match AND ALSO the fields that returned NULL values.

The fields that had a value that DID NOT match eg; fields with the number '7' where returned BUT the 'NULL' values DID NOT return. 

Result I am getting
+---------+-------------+
| message |  viewer  id  |
+---------+-------------+
|    5        |        7        |
+---------+-------------+   

Result that I really want
+---------+-------------+
| message |  viewer  id  |
+---------+-------------+
|    1        |      NULL    |
|    3        |      NULL    |
|    4        |      NULL    |
|    5        |        7        |
+---------+-------------+   

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 August 2006 - 12:47 AM

That's going to be tricky if you're LEFT JOINing, since they will be nulled out -- you'll need to but this condition into your ON clause.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users