Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Blog
Many to many
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
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;
absolute service time: 0,44 sec
fork mode
|
history
|
discussion
hotel_books_count
1
5
tr_id
p_coun
1
tr10
0
2
tr7
1
3
tr13
0
4
tr8
1
5
tr2
2
6
tr14
0
7
tr6
1
8
tr4
8
9
tr11
0
10
tr3
2
11
tr9
1
12
tr5
1
13
tr1
2
14
tr12
0