Field pointing to same table 2

[liark]

I am a bit stumped.

I have a table with an index, the table includes firstname, lastname, and a pointer to the index of the same table, different record.

e.g.

ID, FirstName, Lastname, Pointer

where pointer is the ID of a different record in the same table.

What I want to produce is a report with the firtname and lastname of both the owner of the record, and also the firstname and lastname of the record that the pointer points to in the same table.

Can someone give me a nice clean way of doing this, please?

Liark

 

[LyndonOHRC]

Does the record where id=pointer also have a value in its pointer field that points to yet to yet another record? It sounds to me like you have a great opportunity for recursion with this design. Even if it is not "logical" for this to occur it still possible and at that point a query statement would never stop populating.

I suspect you need to normalize your design here. If there is a relationship between two rows of the same table that relationship should be in a separate table.

Maybe you could post some of your database structure and explain why you are using this methodology.


Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[liark]

Hi Lyndon,

Thanks for the reply. No, the second record points back to the first record.


The reason it exists is because a number of people in the list can select a 'partner' - which comes from the same list. This action populates both records.

Liark

 

[LyndonOHRC]

Proper design would be:

MemberTable:
ID
FisrtName
LastName
Partner (previously called pointer changed to make sense)

PartnerTable:
ID
MemberID

SQL:
Select m.LastName, m.FirstName, p.FirstName as PartnerFisrtName, p.LastName as PartnerLastName
From MemberTable as m, PartnerTable as p
Where #m.Partner#=#p.id#


When you execute the action that connects two partners, populate two records in the partner table with the necessary data.

You could create the partner table and populate it with the existing data using a query that you will only run once and then you will have your database normalized.

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[r937]

i think the original design is fine -- and it is normalized, too

SELECT o.LastName   AS owner_lastname
     , o.FirstName  AS owner_firstname
     , p.LastName   AS partner_lastname
     , p.FirstName  AS partner_firstname
  FROM daTable AS o
LEFT OUTER
  JOIN daTable AS p
    ON p.ID = o.pointer

r937.com | rudy.ca

 

[liark]

Thanks, both.

I have chosen Rudy's solution simply because I don't have to redesign the database, and it works a treat.

Thanks to Lyndon too for your help.

Liark