1

I have a table called "child_parent" which lists ID's of children and their parents, referring to, say, a table called "person". The child-parent pair are unique, and a child can have no more than two parents.

child  parent
3      1
3      2
4      1
7      8

I want a table of the "other" parent (call it "ChildOfpID_OtherParent") of all children with parent ID (pID) equal to, say, 1 with NULL if the other parent is not listed. So if pID=1, I want:

child_of_pID   other_parent
3              2
4              NULL

I'm having trouble figuring out how to generate such a table.

Paul R.
  • 111
  • 2

2 Answers2

2

This query would return the correct result for your example

SELECT p1.child as child_of_pID, p2.parent as other_parent 
FROM child_parent AS p1 
  LEFT JOIN child_parent p2 
  ON p1.parent <> p2.parent AND p1.child = p2.child
WHERE  p1.parent = 1;
Richard St-Cyr
  • 970
  • 1
  • 8
  • 14
0

That works great - I have to study aliases more. I think an AS is missing, and generalizing to pID (=1 in above example), I would write:

SELECT p1.child as child_of_pID, p2.parent as other_parent 
FROM child_parent AS p1 
  LEFT JOIN child_parent AS p2 
  ON p1.parent <> p2.parent AND p1.child = p2.child
WHERE  p1.parent = pID;
Paul R.
  • 111
  • 2