Just found this forum and have learned a tremendous amount browsing around. I've got a problem I hope someone can help me with.

Two tables, A and B. Need to join where A.site_id = B.site_id. Not a problem. Here is where the problem lies - Also need to add a condition if A.doc_status is not null then A.doc_status = B.status. If no records are found then use condition B.archive_flag = 'C'.

Any suggestions? Using DB2 7.2 if that matters.

Here is part of the answer:

WHERE a.site_id = b.site_id
AND (a.doc_status IS NULL OR a.doc_status = b.status)

However, I don't understand the last bit about the archive_flag. Unless you mean:

WHERE a.site_id = b.site_id
AND (a.doc_status IS NULL OR a.doc_status = b.status OR b.archive_flag='C')

Thanks, I got the first part. It's the second part that is confusing both of us. In other words, if the first parts condition produces no rows, then we are going to use a completely different condition, archive_flag, and ignore the first condition.

maybe this is the solution

select a.*,b.*
from a , b
where a.site_id=b.site_id and
(
a.doc_status is null or
a.doc_status=b.doc_status or
(
b.archive_flag='C' and not exists
(select 1 from a a2,b b2 where a.site_id=a2.site_id and a2.site_id=b2.site_id and
(
a2.doc_status is null or
a2.doc_status=b2.doc_status
)
)
)
)
;

That sounds like a cartesian product - for each row in A where condition1 is false, return ALL rows from B where condition2 is true...?

Something like:

Seems very odd...

The code below almost gets it. I guess I need to explain more. My design docs state (Not done by me, BTW):

"For each record in table A if doc_status is not null then access table B where B.status = A.doc_status. If no record matches then use table B where archive_flag = 'C'."

Table A is a list of sites. Table B contains demographic studies for the sites. There is more than one demographic study for each site. What they are trying to get is the demographic study for a site where the status is equal to the site status. If that doesn't exist, then we want to bring back the demographic study for that site whose demo study archive_flag is 'C', for current.

Third time lucky? :-

Thank you! Finally got it to work with some tweaking of the code below. Turns out the design docs were backwards and we also needed to account for the case when only a 'C' record exisited but the statuses did not match . . .