Jump to content

Archived

This topic is now archived and is closed to further replies.

scrunchmuppet

NULL stop...!

Recommended Posts

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...

Share this post


Link to post
Share on other sites
I read this post 3 times... i don't understand.

Share this post


Link to post
Share on other sites
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        |
+---------+-------------+   

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.