如何比较不同列和不同行但同一个表的值？

ID Name FatherID Birthyear

1   Bart    NULL     1756    
2   Franz   1        1796
3   Josef   2        1835    
4   Zohan   3        1887

Suppose I have this table, I would like to know if Zohan is the son of Bart, which can be gotten if I compare the values from the column "FatherID" with the ID of the previous rows up until I get to Bart. But how do I compare the values in the same table but of different rows and columns

假设我有这个表,我想知道Zohan是否是Bart的儿子,如果我将“FatherID”列中的值与前一行的ID进行比较直到我找到Bart,就可以得到它。但是,如何比较同一个表中但不同行和列的值

1 个解决方案

#1

You could self join the table:

你可以自己加入桌子:

SELECT s.name AS son_name, f.name AS father_name
FROM   mytable s
JOIN   mytable f ON s.fatherID = f.id
-- possibly add a where clause with conditions on son/father names