This is a sybase database, which follows t-sql syntax (same/similar to MSSQL).
I have 2 tables, cases and insurance.
CASES insurance
----- ---------
casenum ins_id
date_resolved
case_num
Each case number has multiple associated insurance entries, and each of these insurance entries has a date resolved field.
What I'm trying to do, is return ONLY case numbers where ALL associated insurance entries have date_resolved = null.
What I'm getting is case numbers and the associated insurance entries that are null.
For example, if a case number has 3 insurance entries, 2 of which have a null date_resolved, but 1 does have a value in date_resolved, my query will return the case number because the 2 entries are null.
Here's what I've tried:
select casenum
from cases
left join (select date_resolved from insurance where date_resolved is null) ins
on ins.case_num = cases.casenum
select casenum
from cases
inner join insurance on cases.casenum = insurance.case_num
where insurance.date_resolved is null
I'm sorry if this is a beginner's mistake, I've searched the board and have found many similar questions, but none that seem to address my problem.
EDIT - Using one of the answers provided below, I incorporated it into my query and ended up with something like this (which I believe, works):
select casenum, *other stuff*
from cases
inner join (select i.case_num from insurance i group by i.case_num having max(date_resolved) is null) ins
on ins.case_num = cases.casenum
where *other stuff from cases table*
and *more stuff from cases table*
I really appreciate it!
One method is aggregation:
select i.case_num
from insurance i
group by i.case_num
having max(date_resolved) is null;
Or, you can use not exists:
select c.*
from cases c
where not exists (select 1
from insurance i
where i.case_num = c.case_num and i.date_resolved is not null
);
Note that these are not equivalent. The second returns cases that have no rows in insurance.