如何从多个表中检索数据(postgresql)

Question

我有4个不同的表，它们以以下方式相互链接(我只保留每个表中的重要列，以强调它们之间的关系(：

create TABLE public.country (
country_code  varchar(2) NOT NULL PRIMARY KEY,
country_name  text NOT NULL,

);

create table public.address
(
id                    integer generated always as identity primary key,
country_code          text not null,
CONSTRAINT FK_address_2 FOREIGN KEY (country_code) REFERENCES public.country (country_code)
);

create table public.client_order
(
id                         integer generated always as identity primary key,
address_id                 integer null,
CONSTRAINT FK_client_order_1 FOREIGN KEY (address_id) REFERENCES public.address (id)

);
create table public.client_order_line
(
id                                   integer generated always as identity primary key,
client_order_id                      integer not null,
product_id                           integer not null,
client_order_status_id               integer not null default 0,
quantity                             integer not null,

CONSTRAINT FK_client_order_line_0 FOREIGN KEY (client_order_id) REFERENCES public.client_order (id)
);

我想通过以下方式获取数据：对于每个客户订单行，显示product_id、quantity和country_name(对应于该客户订单行(。

到目前为止我已经尝试过了：

SELECT country_name FROM public.country WHERE country_code = (
SELECT country_code FROM public.address WHERE id = (
SELECT address_id FROM public.client_order WHERE  id= 5
)
)

从client_order_line表中获取给定client_order.id的国家/地区名称。我不知道如何更改它以从client_order_line表中获得上面提到的所有信息，该表看起来像这样：

id  client_order_id. product_id.   status. quantity
1   1                122           0       1000
2   2                122           0       3000
3   2                125           0       3000
4   3                445           0       2000

非常感谢！

Answer 1

您需要一些join-s。

select col.client_order_id, 
col.product_id, 
col.client_order_status_id as status, 
col.quantity,
c.country_name
from client_order_line col
left join client_order co on col.client_order_id = co.id 
left join address a on co.address_id = a.id
left join country c on a.country_code = c.country_code
order by col.client_order_id;

或者，您可以将选择查询用作标量子查询表达式。