Ask Your Question
0

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

asked 2015-12-19 15:10:05 +0100

marcoE gravatar image

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)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-11 22:10:28.108026

1 Answer

Sort by » oldest newest most voted
0

answered 2015-12-20 03:04:50 +0100

doug gravatar image

updated 2015-12-29 03:54:33 +0100

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

edit flag offensive delete link more

Comments

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 .

marcoE gravatar imagemarcoE ( 2015-12-22 18:41:01 +0100 )edit

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.

doug gravatar imagedoug ( 2015-12-22 20:29:25 +0100 )edit

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)

marcoE gravatar imagemarcoE ( 2015-12-24 17:11:22 +0100 )edit

edited some more to address the comment @marcoE

doug gravatar imagedoug ( 2015-12-24 20:29:46 +0100 )edit

I've created the following database with my sql code: https://www.dropbox.com/s/68xx65dn40r...

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

I Know that's a tricky one.

marcoE gravatar imagemarcoE ( 2015-12-28 15:35:11 +0100 )edit

@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.

doug gravatar imagedoug ( 2015-12-29 03:53:26 +0100 )edit

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

marcoE gravatar imagemarcoE ( 2015-12-30 15:44:14 +0100 )edit

@marcoE, thank you for accepting the answer.

doug gravatar imagedoug ( 2015-12-30 17:46:43 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2015-12-19 15:10:05 +0100

Seen: 167 times

Last updated: Dec 29 '15