Selecting customers that follow a particular trend

[bajanstar]

Hi Guys,

This is my problem:

In the below table:

donor receptor
1 -> 4
2 -> 5
4 -> 6

The above reads as follows...

Student 1 donates all of his/her money to Student 4
Student 2 donates all of his/her money to Student 5
Student 4 donates all of his/her money to Student 6

I would like to pull all of the ultimate donors from the above table. An ultimate donor is someone who is at all times a receptor and never a donor.

So in the above scenario Students 5 and 6 are ultimate donors. Does anyone have any ideas on how this can be accomplished?

I will be very appreciative of any inputs.

Thanks

[tesuji]

Hello bajanstar

you may try this statement:

select receptor as 
  'ultimate donors, really? Shouldn''t it be ultimate receptors?' 
      from inthebelowtable where receptor NOT IN 
          (select donor from inthebelowtable)

krs,
tesu

[bajanstar]

hi tesu,
You are correct. It is suppose to be ultimate receptor. Thanks for you input.

so what do you think about this solution

select distinct receptor
from yourtable a
left join (select donor from yourtable) b on a.receptor = b.donor
where b.donor is null

[tesuji]

no, not that a good idea, consider:

left join (there are no null entries, because each left value has its mate in right column)
1 4
2 5
4 6

right join (if right value has no mate in left column, null will be inserted left hand side)
1 4
null 5
null 6

possibly, select looks like:
select b.donor, b.receptor from inthebelowtable a right join inthebelowtable b
on a.receptor = b.receptor where a.donor IS NOT null