CODEWITHSUNDEEP
mysqljoinrelationshipcreate-table
batsta13
batsta13

Reputation: 539

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

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.

enter image description here

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.

Upvotes: 0

Views: 1489

Answers (3)

Eugen Rieck
Eugen Rieck

Reputation: 65284

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'

Upvotes: 0

Daniel Renshaw
Daniel Renshaw

Reputation: 34177

SELECT
    Child.name AS ChildName,
    Parent.name AS ParentName
FROM Guardian
    INNER JOIN Person AS Child ON Child.personID = Guardian.childID
    INNER JOIN Person AS Parent ON Parent.personID = Guardian.parentID

This will return a row for each parent/child pair.

The important feature is to reference the Person table twice but give it a different alias name in each case. You can then refer to that table in two distinct roles. The output columns can also be aliased to distinguish between the same column from the two instances of the joined table.

Upvotes: 1

Diego
Diego

Reputation: 36146

You didnt mentioned, but assuming a person can have only one guardian, I dont think you need two tables.

Just add a guardianID on the Person table that maps to itself. So you would have something like this

Person
ID   name        GuardianID
1    The Father     null
2    The Son         1

so if you want to know a person and its guardian you can do 

select *, (select p2.name from person wherep2.id=p.guardianid)
from person p
where p.id=2

Upvotes: 0

Related Questions