Trying to return Null values in a query with another value

Question

So I have the following data in a table.

ID        Name        fID        mID
1         Bob         135        100
2         James       Null       100
3         Graham      Null       100
4         Gemma       142        100

What I want to do is return Bob, James, Graham, based on the fact that they are all under the mID of 100, but also that the fID is 135 or NULL. This excludes Gemma, since while she is under 100, she has an fID of 142 which is not 135 or NULL.

So far I have tried doing this:

SELECT ID, Name
FROM table1
WHERE mID=100 AND fID IN (SELECT fID FROM table1 WHERE fID=135 AND fID IS NULL)

But that returns nothing. Any suggestions helpful.

Andrei Hirsu · Accepted Answer · 2014-11-13 12:07:35Z

2

SELECT ID, Name
FROM table1
WHERE mID=100 AND (fID = 135 OR fID is NULL)

answered Nov 13, 2014 at 12:07

Andrei Hirsu

6964 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thomas Krojer · Accepted Answer · 2014-11-13 12:19:17Z

0

In your subselect, you should use OR instead of AND.

SELECT ID, Name FROM table1
WHERE mID=100 AND fID IN (SELECT fID FROM table1 WHERE fID=135 OR fID IS NULL)

is wrong, here the correct one:

select id, name from table1 where mid = 100 and id in 
(SELECT ID FROM table1 WHERE fID=135 OR fID IS NULL)

edited Nov 13, 2014 at 12:19

answered Nov 13, 2014 at 12:07

Thomas Krojer

1,0387 silver badges9 bronze badges

6 Comments

user743382 Over a year ago

That wouldn't work. If fID is NULL, fID IN (whatever) will never be true.

Thomas Krojer Over a year ago

here ist the corrected way: select id, name from table1 where mid = 100 and id in (SELECT ID FROM table1 WHERE fID=135 OR fID IS NULL)

user743382 Over a year ago

That would work. It would be needlessly complicated, in my opinion, but it is valid nonetheless. You can edit your answer to say that, and if you do, I'll be happy to retract my downvote.

Thomas Krojer Over a year ago

depending on the SQL dialect (was not told by op), there are better ways, Oracle: NVL, mssql Isnull ....

user743382 Over a year ago

The OP did: it's Microsoft SQL Server. You can tell from the tags. I think you're thinking of ISNULL(fID, 135) = 135, which again is valid, but more complicated than Andrei Hirsu's answer.

|

Collectives™ on Stack Overflow

Trying to return Null values in a query with another value

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related