Using a single person table to store and display related family members

Question

I am wanting to create a query that returns a parent and its guardian using the follow tables. My question is how can I use the name attribute in the person table twice to return the name of the caregiver and the name of the parent. Any example code would be appreciated thanks. I know I havn't explained it very well as but if anyone understand what I mean feel free to edit the above. More specifically I want to use only the person table to store information about parents and their children and use display the name of each who are related. How can I do this with a single table.

Using some advice provided, I was able to create the query but due to a problem with my table it doesn't work. I am using the following tables:

CREATE TABLE Person
(
personID INT NOT NULL,
name VARCHAR(50),
address VARCHAR(70),
phone VARCHAR(15),
email VARCHAR(30),
year INT,
PRIMARY KEY (personID)
);

CREATE TABLE Guardian
(
parentID INT NOT NULL,
childID INT NOT NULL,
PRIMARY KEY (parentID, childID)
FOREIGN KEY (parentID) REFERENCES (personID),
FOREIGN KEY (childID) REFERENCES (personID)
);

What statements should I use where to get this to work properly. I think the problem is that both foreign key statements are referring to the same key in the person table. How do I refer to the same attribute when looking for both the parent and child without getting any errors.

Answer 1

First fix your create table:

CREATE TABLE Guardian
(
parentID INT NOT NULL,
childID INT NOT NULL,
PRIMARY KEY (parentID, childID),
FOREIGN KEY (parentID) REFERENCES Person(personID),
FOREIGN KEY (childID) REFERENCES Person(personID)
);

then query:

SELECT
  p.name as parentName,
  c.name as childName,
  Guardian.*
FROM Person AS p
  INNER JOIN Guardian ON p.PersonID=Guardian.parentID
  INNER JOIN Person AS c ON c.PersonID=Guardian.childID
WHERE
  -- whatever you want, e.g.
  p.Name='John'