postgresql 如何使用内连接查询多个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9587011/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I query multiple tables using inner join?
提问by Aaron
Given the table creation SQL and insertion SQL, How will I generate a query to display the information in a a particular way?
鉴于表创建 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');
Now I want the numerical values that are in the table estate to be displayed by the string values that they represent.
现在我希望表格资产中的数值由它们代表的字符串值显示。
E.G 1 Johannesburg, Westbury, Room, Rent, Description
EG 1 约翰内斯堡，韦斯特伯里，房间，租金，描述
What will the SQL Query be for this, I am more concerned in using postgreSQL.
SQL Query 将用于此，我更关心使用 postgreSQL。
回答by Pranay Rana
you cany try like
你可以试试
select * from table1
inner join table2 on tabl1.pk = table2.FK
inner join table3 on tabl1.pk = table.FK
Final
最后
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
If you want to get info about joins have look to below image
如果您想获取有关加入的信息，请查看下图
Ans at : 回答by Icarus
First, they must have some sort of common field. Let's assume the common field between them is called <tablename>_ID; the way you do it is as follows:
首先，它们必须有某种共同的领域。让我们假设它们之间的公共字段被称为<tablename>_ID; 你这样做的方式如下：
select A.colx, A.coly, A.colz, B.colx, B.colw, c.cold
from A inner join B on A.ID=B.A_ID
inner join C on C.A_ID=A.ID
回答by digital.aaron
select suburbs.icity
, cities.iname
, suburbs.iname
, type.iname
, sale.iname
, estate.idescription
from estate
inner join suburbs on suburbs.id = estate.isuburb
inner join cities on cities.id = estate.icity
inner join type on type.id = estate.itype
inner join sale on sale.id = estate.isale
回答by Sparky
In general, you are looking for a join..
一般来说，你正在寻找一个加入..
select ct.iname as CityName,sb.name as SuburbName,et.*
from estate et
join cities ct on ct.id=et.icity
join suburbs sb on sb.id=et.isuburb
etc...
等等...
回答by Jerec TheSith
You could do it this way :
你可以这样做：
SELECT *
FROM A
INNER JOIN B
ON B.id = A.b
INNER JOIN C
ON C.id = A.c
回答by Teja
Check this example as per ur ques.
根据您的问题检查此示例。
SELECT A.*,B.*,C.*
FROM A , B, C
WHERE C.c = B.b
AND B.b = A.a
回答by kal
-> select * from estate
-> inner join cities on estate.icity=cities.id
-> inner join suburbs on estate.isuburb=suburbs.id
-> inner join type on estate.itype=type.id
-> inner join sale on estate.isale=sale.id;
