Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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.
finaleHFEIZFIDSHI
Finale8E9237394
Shortest "path" between two employees across companies
Pivot the Hard Way..
first
testo
Join elements by key with json fields
book suggestion
Assignment 1(SET A)
nested array to table
Please log in to post a comment.