Return null values

[weee]

Hello everyone,

 

I am trying to force mySQL to return null values rather than no rows on my join.  So here is my scenario

 

Table 1

ID | NAME | VALUE | AnotherID
1   | blah   | 25 | 2
2   | z        | 36 | 2
3   | a        | 1 | 3
4   | b        | 2 | 2

 

Table 2

ID2 | ID | otherValue | OtherValue2
1     | 2   | b   | 4

 

Here would be my sql code

SELECT table1.*, table2.otherValue, table2.otherValue2 FROM table1
LEFT JOIN table2 ON table2.ID=table1.ID
WHERE table1.anotherID=2

 

Now I would like my result to look like the result below but instead I get 0 rows.

 

ID | NAME | VALUE | AnotherID | otherValue | otherValue2
1   | blah   | 25 | 2 | NULL   | NULL
2   | z        | 36 | 2 | b   |4
4   | b        | 2 | 2 | NULL | NULL

 

How can I do this?

 

Thank you!

weee

[fenway]

You can't ever get 0 rows from that query.

[weee]

Right now the only way I can get my queries to return NULL values is if I do something like this:

SELECT table1.*, (SELECT  otherValue2 FROM table2 WHERE ID=table1.ID) as otherValue2 FROM table1
WHERE table1.anotherID=2

[Pikachu2000]

You are aware that NULL !== NULL, right?

[weee]

NULL!==NULL????  How not?  Even if I can get my join to return 0 I would be fine, but right now it is dropping the entire row rather than return something back which is the problem.  This keeps the row in tact regardless if table2 has the value or not.

[Pikachu2000]

NULL doesn't equal anything, not even another NULL. If you attempt to select with a comparison between two fields that are NULL, they will not match. For example, if `name1` and `name2` are both NULL in `table`, and you try to:

SELECT FROM `table` WHERE `name1` = `name2`

the query will return an empty result set.

 

I'm not sure if that's contributing to the problem here, but it's something you should be aware of.

[weee]

My scenario is that I have a value=NULL and that is resulting in 0 rows on my join, rather than replying back as onn on my second table.

[Muddy_Funster]

so, just to clear this up :

Have you got NULL in as a string value (i.e. is NULL actualy written into the field) or is it a NULL value (i.e. you have set the default value to be NULL which in turn meens that there is no value to address at all)

[kickstart]

My scenario is that I have a value=NULL and that is resulting in 0 rows on my join, rather than replying back as onn on my second table.

 

With the SQL you have posted I do not see how as you are not comparing any of the columns with NULL values with any other columns.

 

Looking at it, it should give what you want it to give. Possible that the problem is something you have removed to provide a simple piece of SQL for this thread so might be worth putting the full table declares up and the actual SQL if different.

 

All the best

 

Keith

[weee]

Basically I am trying to join a table that has all of the results (my left table) with a table that may have results or may not (my right table).  So I want every row to be returned on my left table regardless if the right table does not have a sufficient row to join (Ie. return back null for not finding anything).

[Muddy_Funster]

ohh, well in that case you just have your join statement back to front (I had to build a duplicate of your sample data into my test area to notice it though ), try this :

SELECT table1.id, table1.name, table1.value, table1.anotherID, table2.otherValue, table2.otherValue2 FROM 
table1 LEFT JOIN table2 ON (table1.id = table2.id)

You can use whatever WHERE your working with, I didn't need one obviously as the dataset was nice and small.

[fenway]

<=> is null-safe.