mysql - HSQL - 如何编写查询来获取 1 :n reslationship IDs

Question

可能标题不正确。我是数据库新手。

我有这个具有这种类型关系的表:

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")
);

我怎样才能获得(例如)对方父亲的手机？

Answer 1

尝试加入用户和家庭表，例如:

SELECT *
FROM User u INNER JOIN Family f
ON   u.id_family = f.id_family
WHERE u.name = 'xyz'