join_example.sql

Queries for create all needed tables:

create table if not exists customers(customer_id integer primary key, customer_login text, customer_name text, unique(customer_login))
create table if not exists goods(good_id integer, good_description text, unique(good_id))
create table if not exists orders(order_id integer primary key, customer_id integer, good_id integer, unique(order_id, customer_id, good_id))

Queries to populate tables:

insert into customers(customer_login, customer_name) values("user_login1", "customer_name1")
insert into customers(customer_login, customer_name) values("user_login2", "customer_name2")
insert into customers(customer_login, customer_name) values("user_login3", "customer_name3")
insert into goods(good_id, good_description) values(1, "good_description1")
insert into goods(good_id, good_description) values(2, "good_description2")
insert into goods(good_id, good_description) values(3, "good_description3")

insert into orders(customer_id, good_id) values(2, 3)
insert into orders(customer_id, good_id) values(2, 1)
insert into orders(customer_id, good_id) values(2, 2)
insert into orders(customer_id, good_id) values(3, 2)
insert into orders(customer_id, good_id) values(1, 3)

Result query, that shows how many orders done by each customers:

SELECT cs.customer_name, COUNT(o.order_id) count_of_orders FROM customers cs JOIN orders o on cs.customer_id = o.customer_id GROUP BY cs.customer_name