Referencing within a table

[krisboy]

Hello all,

Hopefully someone can help me out here!

I have a table which includes the following fields:

Client_Ref, Client_Name, Partner_Ref

Each client is either an end user or a partner. If they are an end user, their partner_ref references the client who deals with them by their client_ref. In other words, if the partner_ref of one client matches the client_ref of another, they have a Partner/End User relationship. Ok so far - what I want to do is list the end users for each Partner. However, I do not particularly want to make a copy of the table (it is quite large) and so want to know if there is any way I can query this table to get the desired result? To a beginner like me, the problem appears to be that fields within the table reference each other... Any ideas would be welcome.

Thanks.
Chris.

 

[r937]

select t1.Partner_Ref
, t1.Client_Name as Partner_Name
, t2.Client_Ref
, t2.Client_Name
from yourtable t1
left outer
join yourtable t2
on t1.Client_Ref = t2.Partner_Ref
where t1.Partner_Ref is null

notice i have assumed that the way you distinguish the partners from the clients is that a partner does not have a partner ref (i.e. it's null)

rudy

http://rudy.ca/