capture.png I have a table called Person consisting of Columns PersonName, FatherName, and MaritualStatus. I would like to create a query which gives me PersonName, FatherName, and MaritalStatus in which the Person's all sons are married. In my table below I want row 1 as a result of the query.
You want to use NOT EXISTS, something like so:
SELECT *
FROM Person outerTable
WHERE NOT EXISTS(
SELECT *
FROM Person innerTable
WHERE innerTable.Person = outerTable.FatherName
AND innerTable.MaritalStatus = 'UnMarried'
)
But that said, I don't know that you want to use names, you should give each person an ID somehow (numeric or alphanumeric or some other unique identifier) and join on that. My ancestors were not always very original, there are several generations where I can't be sure what person I'm looking at just by name, I have to look at dates to figure it out. (was it the son, the grandson or the great grandfather? they all have the same name)
19 People are following this question.
