easy family tree query-CodePudding

There is only one table.

Table name Family_tree.

Query all children for Nick. Nick can be also mother!

NAME                 ID    FATHER_ID    MOTHER_ID
--------------- ---------- ---------- ----------
Nick                  23         25        24
Jane                  10         27        26
Perl                  15         9         13
Katrin                50         6         12
Sandra                1          3          8
Demi                  2          3          8
Deimar                3          7          5
Gandalf               4          6          5
Bill                  5         10         23
Kelly                 6         22         43
Dolmar                7         11         20

CodePudding user response：

select t1.name as child, t2.name as father
from table t1 join table t2 on t1.code = t2.father_id

This will give the following o/p

Child      Father
______________________________
Nick        fill from your tab
Calvin      fill from your tab
Sofia       Nick

CodePudding user response：

If you're saying that Code = 3 means that Nick is that person's father:

If you only want to return the name:

SELECT Name FROM table WHERE code = 3

IF you want to return everything:

SELECT * FROM table WHERE code = 3

The '3' might have to be in single-quotes as I typed in this sentence, depending on the data type of the Code field the particular SQL dialect.

EDIT: not quite sure I understand the ask, but Rajee's answer might correct. You can join a table to itself with aliasing i.e. Family_Tree A, Family_Tree B

Another possibility would be a subquery. Because I'm not sure understand the ask, I don't know if this is what you're looking for but consider it an example of what a subquery would look like:

SELECT * FROM Family_Tree A WHERE FatherID = (SELECT ID From Family_Tree B WHERE Other Conditions = Other Conditions)

Something like that, but same thing could probably be accomplished with my first answer or Rajee's answer with some more clarification.

CodePudding user response：

You can try below code -

SELECT 
  FROM Family_tree T1
  JOIN (SELECT ID, FATHER_ID
          FROM Family_tree
         UNION ALL
        SELECT ID, MOTHER_ID
          FROM Family_tree) T2 ON T1.ID = T2.FATHER_ID;