how to retrieve data from multiple tables (postgresql)-CodePudding

I have 4 different tables that are linked to each other in the following way (I only kept the essential columns in each table to emphasise the relationships between them):

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

I want to get the data in the following way: for each client order line to show the product_id, quantity and country_name(corresponding to that client order line).

I tried this so far:

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

to get the country name given a client_order_id from client_order_line table. I don't know how to change this to get all the information mentioned above, from client_order_line table which looks like this:

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

Thanks a lot!

CodePudding user response：

You need a few 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;

Alternatively you can use your select query as a scalar subquery expression.