I have two tables (table/column names not my choice):
| Person | | Parent | ------------------------------ ----------------------- | Person | Name | Sex | | Person | Parent |
In the Parent table, each Person (besides great-grandparents) is there twice - once for each parent.
I am trying to find all pairs of full siblings, and output them in the same row. The issue (I think) is that the Child2 join (commented out) will cause no values to be returned, since Parent1.Person and Parent2.Person can only hold one value at a time (that of the first sibling).
Here is my code that is closest to my desired output:
SELECT Child.Person, Child2.Person, Parent1.Parent, Parent2.Parent, Child.Name AS Sibling1, Person1.Name AS Father, Person2.Name AS Mother -- , Child2.Name AS Sibling2 FROM Parent Parent1 INNER JOIN Parent Parent2 ON (Parent1.Person = Parent2.Person) INNER JOIN Person Person1 ON (Parent1.Parent = Person1.Person) INNER JOIN Person Person2 ON (Parent2.Parent = Person2.Person) INNER JOIN Person Child ON (Child.Person = Parent1.Person AND Child.Person = Parent2.Person) -- LEFT JOIN Person Child2 ON (Child2.Person = Parent1.Person AND Child2.Person = Parent2.Person AND Child2.Person != Child.Person) WHERE Parent1.Parent <> Parent2.Parent AND Person1.Sex = 'F' AND Person2.Sex = 'M';
Is there a way for me to check if Child2.Person is in any row where both Parent1.Person/Parent2.Person are the parents?
Another thing I tried was replacing the Child2 join with a "sub-join" where I get the Child2 parents (say Parent3 and Parent4), and then outside of the sub-join compare Child1 parents to Child2 parents.
I gave that "sub-join" an alias so I could use it to compare. However, my idea didn't work because I couldn't access both the Parent columns (Parent3.Parent, Parent4.Parent) using the "sub-join" alias, since it was considered ambiguous.
I'm new to SQL (we are working with Oracle specifically, not sure how much that matters) so I am sure I worded my question horribly, but I've worked on this assignment for a few hours every day for the past 3 or 4 days. It probably means I am an idiot, but I have finally decided to ask for help.
Searching for it has only given me bits and pieces of things that are semi-related, as I am not sure how to word it exactly.
I'd appreciate any help I can get, honestly. Even just pointing me in the right direction would be amazing. I am definitely "tunnel visioned" at this point and am likely missing something super obvious.