Many to many, PostgreSQL

Many to many

CREATE TABLE peoples (
pe_id varchar PRIMARY KEY,
name varchar
);

CREATE TABLE travels (
tr_id varchar PRIMARY KEY,
owner_id varchar,
town varchar
);

CREATE TABLE hotel_books (
hb_id varchar PRIMARY KEY,
pe_id varchar,
tr_id varchar,
hotel varchar
);

insert into peoples values('pe1', 'Nike');
insert into peoples values('pe2', 'Mike');
insert into peoples values('pe3', 'Dike');
insert into peoples values('pe4', 'Zike');
insert into peoples values('pe5', 'Pike');

insert into travels values('tr1', 'pe1', 'Paris');
insert into travels values('tr2', 'pe2', 'Bern');
insert into travels values('tr3', 'pe3', 'Madrid');
insert into travels values('tr4', 'pe4', 'NY');
insert into travels values('tr5', 'pe5', 'LA');
insert into travels values('tr6', 'pe1', 'London');
insert into travels values('tr7', 'pe1', 'Berlin');
insert into travels values('tr8', 'pe2', 'Rio');
insert into travels values('tr9', 'pe2', 'Washington');
insert into travels values('tr10', 'pe3', 'Kalkuta');
insert into travels values('tr11', 'pe3', 'Medina');
insert into travels values('tr12', 'pe4', 'Zagreb');
insert into travels values('tr13', 'pe4', 'Turin');
insert into travels values('tr14', 'pe1', 'Olimp');

insert into hotel_books values('hb1', 'pe1', 'tr1', 'Kimel');
insert into hotel_books values('hb2', 'pe2', 'tr2', 'Tynel');
insert into hotel_books values('hb3', 'pe3', 'tr3', 'Perel');
insert into hotel_books values('hb4', 'pe4', 'tr4', 'Turel');
insert into hotel_books values('hb5', 'pe5', 'tr5', 'Mawel');
insert into hotel_books values('hb6', 'pe5', 'tr6', 'Omel');
insert into hotel_books values('hb7', 'pe5', 'tr7', 'Gamel');
insert into hotel_books values('hb8', 'pe4', 'tr8', 'Remel');
insert into hotel_books values('hb9', 'pe4', 'tr9', 'Vemel');
insert into hotel_books values('hb10', 'pe5', 'tr1', 'Qarel');
insert into hotel_books values('hb11', 'pe4', 'tr2', 'Jawel');
insert into hotel_books values('hb12', 'pe4', 'tr3', 'Xamel');
insert into hotel_books values('hb13', 'pe3', 'tr4', 'Zemel');
insert into hotel_books values('hb14', 'pe5', 'tr4', 'Femel');
insert into hotel_books values('hb15', 'pe5', 'tr4', 'Remel');
insert into hotel_books values('hb16', 'pe4', 'tr4', 'Hemel');
insert into hotel_books values('hb17', 'pe4', 'tr4', 'Jemel');
insert into hotel_books values('hb18', 'pe5', 'tr4', 'Pemel');
insert into hotel_books values('hb19', 'pe5', 'tr4', 'Lemel');

--------------------------------------------------------------
SELECT 
  count(hotel_books.pe_id) as hotel_books_count
from hotel_books
JOIN travels ON hotel_books.tr_id = travels.tr_id
WHERE travels.owner_id = 'pe4'
and hotel_books.pe_id <> 'pe4';
--------------------------------------------------------------

select
  travels.tr_id,
  count(hotel_books.pe_id <> travels.owner_id) as p_coun
from travels
LEFT OUTER JOIN peoples on travels.owner_id = peoples.pe_id
LEFT OUTER JOIN hotel_books on travels.tr_id = hotel_books.tr_id
group by travels.tr_id;


run \| edit \| history \| help	0

Please log in to post a comment.

stackse - search stackoverflow differently