Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version
SELECT "child"."name" AS "childName", "father"."Cellphone Number" AS "fatherCell"
FROM  "User" AS "child" 
LEFT JOIN "Family" AS "family" ON ("f"."ID Family" = "child"."ID Family")
LEFT JOIN "User" AS "father" ON ("f"."ID Family" = "father"."ID Family" AND "f"."ID Father" = "father"."ID User")
WHERE "child"."name" = 'enterNameHere'

I am making an assumption that the ID Father refers to the ID User field. If it is unique, then on the second join you could just match on that.

SELECT "child"."name" AS "childName", "father"."Cellphone Number" AS "fatherCell"
FROM  "User" AS "child" 
LEFT JOIN "Family" AS "family" ON ("f"."ID ("family"."ID Family" = "child"."ID Family")
LEFT JOIN "User" AS "father" ON ("f"."ID ("family"."ID Family" = "father"."ID Family" AND "f"."ID "family"."ID Father" = "father"."ID User")
WHERE "child"."name" = 'enterNameHere'

I am making an assumption that the ID Father refers to the ID User field. If it is unique, then on the second join you could just match on that.

SELECT "father"."name" AS "fatherName", "child"."name" AS "childName", "father"."Cellphone "child"."Cellphone Number" AS "fatherCell"
"childCell"
FROM  "User" AS "child" 
"father"
LEFT JOIN "Family" AS "family" ON ("family"."ID Family" = "child"."ID Family")
LEFT JOIN "User" AS "father" ON ("family"."ID Family" = "father"."ID Family" AND "family"."ID Father" = "father"."ID User")
LEFT JOIN "User" AS "child" ON ("family"."ID Family" = "child"."ID Family")
WHERE "child"."name" "father"."name" = 'enterNameHere'

I am making an assumption that the ID Father refers to the ID User field. If it is unique, then on the second join you could just match on that.that. I you are not getting all the records you want, check the consistency of the Family ID and the ID Father.

I have not verified the syntax.

-- edited to allow finding children by parent.

SELECT "child"."name" AS "childName", "father"."name" AS "fatherName", "child"."name" AS "childName", "child"."Cellphone "father"."Cellphone Number" AS "childCell"
"fatherCell", "mother"."name" AS "motherName", "father"."Cellphone Number" AS "motherCell"
FROM  "User" AS "father"
LEFT "child"
JOIN "Family" AS "family" ON ("family"."ID Family" = "father"."ID Family" AND "family"."ID Father" "child"."ID Family")
LEFT JOIN "User" AS "father" ON ("family"."ID father" = "father"."ID User")
LEFT JOIN "User" AS "child" "mother" ON ("family"."ID Family" mother" = "child"."ID Family")
WHERE "father"."name" = 'enterNameHere'
"mother"."ID User")

I am making an assumption that the ID Father refers to the ID User field. If it is unique, then on the second join you could just match on that. I you are not getting all the records you want, check the consistency of the Family ID and the ID Father.

Simply add the columns you want at the top separated by commas, with "mother" referring to the version of the "Users" table JOINed under the alias "mother", a dot . and then the name of the column in that table, followed by a descriptive alias for what the column is.

I have not verified the syntax.

-- edited to allow finding children by parent.

parent. -- 2d edit per comment to pull full list and both parents

SELECT "child"."name" "child"."Name" AS "childName", "father"."name" "father"."Name" AS "fatherName", "father"."Cellphone Number" AS "fatherCell", "mother"."name" "mother"."Name" AS "motherName", "father"."Cellphone "mother"."Cellphone Number" AS "motherCell"
FROM  "User" AS "child"
JOIN "Family" AS "family" ON ("family"."ID Family" = "child"."ID Family")
LEFT JOIN "User" AS "father" ON ("family"."ID father" = "father"."ID User")
LEFT JOIN "User" AS "mother" ON ("family"."ID mother" = "mother"."ID User")

I am making an assumption that the ID Father refers to the ID User field. If it is unique, then on the second join you could just match on that. I you are not getting all the records you want, check the consistency of the Family ID and the ID Father.

Simply add the columns you want at the top separated by commas, with "mother" referring to the version of the "Users" table JOINed under the alias "mother", a dot . and then the name of the column in that table, followed by a descriptive alias for what the column is.

I have not verified the syntax.

-- edited to allow finding children by parent. -- 2d edit per comment to pull full list and both parents

parents -- 3d edit per comment - capitalization corrected on field name + table ref typo

SELECT "child"."Name" AS "childName", "father"."Name" AS "fatherName", "father"."Cellphone Number" AS "fatherCell", "mother"."Name" AS "motherName", "mother"."Cellphone Number" AS "motherCell"
FROM  "User" AS "child"
JOIN "Family" AS "family" ON ("family"."ID Family" = "child"."ID Family")
LEFT JOIN "User" AS "father" ON ("family"."ID father" = "father"."ID User")
LEFT JOIN "User" AS "mother" ON ("family"."ID mother" = "mother"."ID User")
WHERE "father"."ID User" != "child"."ID User" 
AND "mother"."ID User" != "child"."ID User"

I am making an assumption that the ID Father refers to the ID User field. If it is unique, then on the second join you could just match on that. I you are not getting all the records you want, check the consistency of the Family ID and the ID Father.

Simply add the columns you want at the top separated by commas, with "mother" referring to the version of the "Users" table JOINed under the alias "mother", a dot . and then the name of the column in that table, followed by a descriptive alias for what the column is.

I have not verified the syntax.

-- edited to allow finding children by parent. -- 2d edit per comment to pull full list and both parents -- 3d edit per comment - capitalization corrected on field name + table ref typo

typo + added WHERE clause