self-join or subquery

[markgargan]

Hey folks,
I have two tables that describe a family

First is person
create person (id integer, name varchar(20));

Second is relationship

create relationship (
relName varchar(16),
fromId integer,
toId integer);

Person
id name
-----------------
1 Dad
2 Mom
3 Mick
4 Jane

Relationship
relname fromId toId
---------------------------------
Husband 1 2
Father 1 3
Father 1 4
Mother 2 3
Mother 2 4
Sibling 3 4


What I'm looking for is some form of sql that will
allow me to create a result set which substitutes the
id's from the relationship table with the names in the person table. i.e. the resultset would look like

Relationship
relname fromId toId
---------------------------------
Husband Dad Mom
Father Dad Mick
Father Dad Jane
Mother Mom Mick
Mother Mom Jane
Sibling Mick Jane

I thought this would be easy to do.
I was wrong.

Any help greatly appreciated.

Thanks,
Mark.

 

[JamesLean]

SELECT r.relname,
  p1.name AS fromname,
  p2.name AS toname
FROM relationship r
  INNER JOIN person p1 ON r.fromid = p1.id
  INNER JOIN person p2 ON r.toid = p2.id

--James

 

[markgargan]

Hi James,

Thank you very much James.
I find it hard to get my head around joins.
Would you have any easy way for remembering the semantics?

Thanks,
Mark.

 

[omegatemplar]

Mark,

INNER JOIN works on records that meet the conditions of the ON statement, these records are considered to be 'inside' the join.

Hope this helps!

 

[markgargan]

Thanks again for your help James I'll try
and keep that in mind with a few examples.

Cheers,
Mark.

 

[PHV]

You may have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[markgargan]

Hey PHV,

Thanks a million for that website.
That's exactly what I was looking for. It nailed all the
conceptual problems I had with joins. Thanks again
for taking the time to post it on to me.

Cheers,
Mark.