Re: Kidney Donor Problem - SQL Solution?

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

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