Re: Kidney Donor Problem - SQL Solution?

Hi Jerry

Let's assume the following:

1. We do not know how many criteria there are
2. There has to be a match for all criteria
3. There are separate tables for recipient and donor criteria (reciptbl, donortbl) with columns for name and criterion (namecol, critcol)
4. These tables are completely populated as to criteria per recipient/donor

I see this as finding a combination of donor & recipient that has the number of matching criteria equal to the total possible.

with critlist as (select distinct substr(critcol, 1, 2) as critcol from donortbl),
critcount as (select count(*) as ccount from critlist)
select d.namecol, r.namecol
from donortbl join reciptbl r on d.critcol = r.critcol
group by d.namecol, r.namecol having count(*) = (select ccount from critcount)

Whew! Hope that is close! It IS early, and I'm on vacation, sort of!

Vern

On 4/10/2012 6:56 AM, Jerry C. Adams wrote:

A friend of mine recently had kidney cancer. As a result one kidney was
removed. The other one seems to be holding its own. The possibility always
exists, though, that the cancer may resurface meaning that he would lose the
remaining kidney and require a kidney transplant sooner than later.



With that background I remember a news story a few years ago about seven
recipients and seven donors in the largest group transplant in history at
the time (Johns Hopkins, I think). Each recipient had a prospective donor
but were not a match. There are three basic matching criteria: Bllod-type,
tissue matching, and crossmatching. There are six factors in the tissue
matching and ten to fifteen factors in the crossmatching.



That led me to wondering how the matches were (and could be) made. I will
admit to being a lightweight when it comes to SQL, but, I think, I'm pretty
good at RPG. I couldn't come close using either to working up a process
(except brute force) that would match sets of recipient-donor. For example,
if only five factors are considered (for illustration purposes), each set
might look like:



Recipient-1 Donor-1

========== =====

F1=A F1=A

F2=B F2=A

F3=A F3=B

F4=A F4=A

F5=C F5=C



Where Fx = Factor and the right side is the result of the factor (result A
for Factor 1 is not the same thing as result A for Factor 2, by the way).



Donor-1 might eventually be found to be compatible for Recipient-7, Donor-5
might be compaticle with Recipient-1, etc. But one does not know in advance
how many Recipient:Donor sets will be necessary to satisfy the situation in
which all recipients are matched with compatible donors.



So, how would one go about solving this problem (for five factors only) in
SQL? Would another approach (RPG, Cobol, etc.) work better, faster?



Jerry C. Adams

IBM i Programmer/Analyst

You don't need to be "straight" to fight and die for your country. You just
need to shoot straight. -Barry Goldwater, author of "Conscience of a
Conservative" in a letter to the Washington Post on 10 June 1993.

--

A&K Wholesale

Murfreesboro, TN

615-867-5070