How can I get the query in the 1:n relationship?

[marcoE]

Hello !
Probably the title it is not correct. I’m a newbie in Database.

I have this tables with this type of relationship:

DROP TABLE "User" IF EXISTS;
DROP TABLE "Address" IF EXISTS;
DROP TABLE "Family" IF EXISTS

CREATE TABLE "Family" (
"ID Family" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT    NULL PRIMARY KEY,
"ID father" INTEGER NOT NULL,
"ID mother" INTEGER NOT NULL,
"Family Name" VARCHAR(25) NOT NULL
);

CREATE TABLE "Address" (
"ID Address" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT   NULL PRIMARY KEY,
"Phone Number" INTEGER,
"Street" VARCHAR(25) NOT NULL,
"Number" INTEGER NOT NULL,
"Floor" INTEGER ,
"City" VARCHAR(15),
"Country" VARCHAR(10)
);
CREATE TABLE "User" (
"ID User" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIM ARY KEY,
"Name" VARCHAR(25) NOT NULL,
"Sex" CHAR(15),
"ID Address"  INTEGER NOT NULL,
"ID Family" INTEGER NOT NULL,
"BirthDate " DATE,
"Cellphone Number" INTEGER,
"mail Address" VARCHAR(25),
"Date" DATE DEFAULT CURRENT_DATE,
CONSTRAINT "CK_Uti_GNDR" CHECK( "Sex" in ( 'Male', 'Female' ) ),
CONSTRAINT FK_Uti_Address FOREIGN KEY ("ID Address") REFERENCES "Address" ("ID Address"),
CONSTRAINT FK_Uti_Family FOREIGN KEY ("ID Family") REFERENCES "Family" ("ID Family")
);

How can I get (for instance) the one’s father cellphone?
(I can give you the odb file, but I’m not allowed to upload files. I don’t have enough points)

[doug]

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 + added WHERE clause

[marcoE]

Thank you for answering.
I’m struggling to make it working.
The Father’s ID, and the Mother’s ID Are the same of the User ID.
All are users, some are organized as family, others not.

I’d like to list all parent’s contacts (mail, cellphone, etc) to each and every child .

[doug]

so you want to start with a parent name, and then list all of the contact information of the children? I’ll flip the JOINs upside down.

[marcoE]

Thanks. I did not explain myself very well.
Iwant to create a list with All users (who are child) and their’s parents’ contacts (mum and dad)

[doug]

edited some more to address the comment @marcoE

[marcoE]

I’ve created the following database with my sql code:

Dropbox - Error

Then I ran your SQL query code and I’ve got error :\

I Know that’s a tricky one.

[doug]

@marcoE – field Name is case sensitive, that was the error, and also fixed typo in reference to mother copy of Users table, and added new limitation so that mother and father do not get a row each as child. SQL tests ok on dropbox table.

[marcoE]

It works.
I do not have enough points do validate your answer :\

[doug]

@marcoE, thank you for accepting the answer.