mysql - 如何使用内连接查询多个表？

Question

给定建表 SQL 和插入 SQL，我将如何生成查询以特定方式显示信息？

CREATE TABLE cities (
   id serial NOT NULL UNIQUE PRIMARY KEY,
   iname varchar(100) NOT NULL UNIQUE
)

CREATE TABLE suburbs (
    id serial NOT NULL UNIQUE PRIMARY KEY,
    icity integer REFERENCES cities (id), 
    iname varchar(100) NOT NULL UNIQUE
)
CREATE TABLE type (
   id serial NOT NULL UNIQUE PRIMARY KEY,
   iname varchar(100) NOT NULL UNIQUE
)
CREATE TABLE sale (
   id serial NOT NULL UNIQUE PRIMARY KEY,
   iname varchar(100) NOT NULL UNIQUE
)

CREATE TABLE estate (
    id serial NOT NULL UNIQUE PRIMARY KEY,
    icity integer REFERENCES cities (id),
    isuburb integer REFERENCES suburbs (id),
    itype integer REFERENCES type (id),
    isale integer REFERENCES sale (id),
    idescription text,
    itimestamp timestamp DEFAULT CURRENT_TIMESTAMP
)

INSERT INTO cities (iname) VALUES ('Johannesburg');
INSERT INTO suburbs (icity, iname) VALUES (1, 'Westbury');
INSERT INTO type (iname) VALUES ('Room');
INSERT INTO sale (iname) VALUES ('Rent');

INSERT INTO estate (icity, isuburb, itype, isale, idescription) VALUES (1, 1, 1, 1, 'A Nice place in a real bad neighbor hood');

现在我希望表中的数值按它们所代表的字符串值显示。

E.G1 Johannesburg, Westbury, 房间，租金，描述

这个SQL Query会是什么，我比较关心的是用postgreSQL。

Answer 1

你可以试试

select * from table1  
inner join table2 on tabl1.pk = table2.FK 
inner join table3 on tabl1.pk = table.FK 

最终

select table2.iname,table3.iname,table4.iname,table1.idescription 
from estate  as table1 
inner join sale  as table2 on table1.isale = table2.id 
inner join  type  as table3 on table1.itype  = table3.id 
inner join  suburbs  as table3 on table1.isuburb = table3.id 
inner join  cities as table4 on table1.icity = table4.id 

如果您想获取有关连接的信息，请查看下图

答案:How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?