joins.sql

begin;

create table users (id integer, name text);
create table segments (id integer, name text);
create table user_segment (user_id integer, segment_id integer);

insert into users (id, name) values (1, 'david');
insert into users (id, name) values (2, 'davido');
insert into users (id, name) values (3, 'looi');
insert into users (id, name) values (4, 'oliver');
insert into users (id, name) values (5, 'zachary');
insert into users (id, name) values (6, 'wei');

insert into segments (id, name) values (1, 'melbourne');
insert into segments (id, name) values (2, 'sydney');
insert into segments (id, name) values (3, 'tasmania');

insert into user_segment (user_id, segment_id) values (1, 1);
insert into user_segment (user_id, segment_id) values (2, 2);
insert into user_segment (user_id, segment_id) values (3, 3);
insert into user_segment (user_id, segment_id) values (4, 1);
insert into user_segment (user_id, segment_id) values (5, 2);
insert into user_segment (user_id, segment_id) values (6, 3);

select * from users
join user_segment on users.id = user_segment.user_id
join segments on segments.id = user_segment.segment_id
where segments.name = 'melbourne';

rollback;