0

So I found these 2 articles but they don't quite answer my question...

Find max value and show corresponding value from different field in SQL server

Find max value and show corresponding value from different field in MS Access

I have a table like this...

ID      Type        Date
1       Initial      1/5/15 
1       Periodic     3/5/15
2       Initial      2/5/15  
3       Initial      1/10/15
3       Periodic     3/6/15  
4        
5       Initial      3/8/15

I need to get all of the ID numbers that are "Periodic" or NULL and corresponding date. So I want a to get query results that looks like this...

ID     Type    Date
1    Periodic  3/5/15
3    Periodic  3/6/15
4

I've tried

select id, type, date1
from Table1 as t
where type in (select type
               from Table1 as t2
               where ((t2.type) Is Null) or "" or ("periodic"));

But this doesn't work... From what I've read about NULL you can't compare null values... Why in SQL NULL can't match with NULL?

So I tried

SELECT id, type, date1
FROM Table1 AS t
WHERE type in (select type
               from Table1 as t2
               where ((t.Type)<>"Initial"));

But this doesn't give me the ID of 4...

Any suggestions?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
AccessProgrammer
  • 11
  • 1
  • 5

1 Answers1

0

Unless I'm missing something, you just want:

select id, type, date1
from Table1 as t
where (t.type Is Null) or (t.type = "") or (t.type = "periodic");

The or applies to boolean expressions, not to values being compared.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for your response! Ya, but then if I add to the original set of data "4 Periodic 3/6/15", I get both "4 Periodic 3/6/15" and "4"... So if there are both, I need to just get the "4 Periodic 3/6/15"... That's why I was trying to match them... – AccessProgrammer May 31 '17 at 17:16