1

Table 2

ID  DATE
--  ----------
01  2018-04-01
03  2017-06-23

I am having hard time trying to write a SQL that will return the ID that has no date or minimum date based on the IDs

for example, for ID 01, 03 only, the sql should return 03 because between 01 and 03, 03 has the earlier date.

but for ID 01,02,03 only, the sql should return 02 because 02 has no data.

for all IDs, 01,02,03,04 the sql can return either 02 or 04.

I've been playing around with MIN(DATE) and NOT EXISTS clauses but no luck because I do not know how to return the empty data.

Any sample SQL is greatly appreciated!

SQL I tried

SELECT MIN(CASE WHEN DATE IS NULL THEN '1900-01-01' ELSE DATE END)
FROM TABLE2
WHERE ID IN (01,02,03,04)

but above sql will give me the min of existing dates. won't default non existing date to 1900-01-01.

Improve this question
3
  • 1
    use isnull(date, '1900-01-01') - this will default any nulls to 1900-01-01 Commented Apr 16, 2018 at 20:05
  • DB2, it's NVL(date, '1900-01-01') but it still returns null, not the default value, so your suggestion didn't work Commented Apr 16, 2018 at 20:14
  • see my full answer below Commented Apr 16, 2018 at 20:16

4 Answers 4

Reset to default
1

use left outer join:

select t1.name, t2.date from Table1 t1 left join Table 2 t2 on t1.id = t2.id

then use isnull on t2.date to stick anything you want in that field.

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

1 Comment

Thanks, LEFT JOIN did help!
1

Assuming that the values that you want to check is listed in table 1

SELECT MIN(CASE WHEN table2.DATE IS NULL THEN '1900-01-01' ELSE table2.DATE END) as output
FROM TABLE1
left join TABLE2
  on table1.ID = table2.ID
Improve this answer

1 Comment

Thanks, LEFT JOIN did help!
1

Use full join in case there are values that don't match in either table

select a.id, a.name, min(isnull(b.date, '1900-01-01')) mindate from #table1 a 
full join  #table2 b on a.id=b.id  
group by a.id, a.name
Improve this answer

6 Comments

Thank you. It's not quite what I'm looking for but you gave me a direction.
OK good - how can I help further? What exactly is still incorrect?
I realized that I do not even need the table 1. so I edited the question but many people already answered using table 1.
OK I see- so just apply the same logic without the join then
Thank, but must be db2 thing. it still returns empty rows.
|
1

How about something like this? Good luck!

SELECT t1.id FROM t1 LEFT JOIN t2 ON t2.id = t1.id LEFT JOIN ( SELECT MIN(COALESCE(date, DATE('2000-01-01'))) AS minimum_date FROM t2 ) minimum_date ON minimum_date = COALESCE(t2.date, DATE('2000-01-01')) ;

Improve this answer

2 Comments

Thanks, LEFT JOIN did help!
@AlisonAftra - Please accept this as the answer, if this resolved your question. It looked like it did.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.