table joins reminder

table_joins.sql

drop table FImage cascade;
drop table Tag cascade;
create table FImage(id integer primary key, url varchar(255));
create table Tag(id integer primary key, text varchar(255));
create table FImageTag(fimage_id integer references FImage(id), tag_id integer references Tag(id));
insert into FImage(id, url) values(1, 'blah.com/im01.jpg');
insert into FImage(id, url) values(2, 'blah.com/im02.jpg');
insert into FImage(id, url) values(3, 'blah.com/im03.jpg');
insert into FImage(id, url) values(4, 'blah.com/im04.jpg');
insert into FImage(id, url) values(5, 'blah.com/im05.jpg');
insert into FImage(id, url) values(6, 'blah.com/im06.jpg');
insert into FImage(id, url) values(7, 'blah.com/im07.jpg');
insert into FImage(id, url) values(8, 'blah.com/im08.jpg');
insert into FImage(id, url) values(9, 'blah.com/im09.jpg');
insert into Tag(id, text) values(1, 'Tag 1');
insert into Tag(id, text) values(2, 'Tag 2');
insert into Tag(id, text) values(3, 'Tag 3');
insert into Tag(id, text) values(4, 'Tag 4');
insert into Tag(id, text) values(5, 'Tag 5');
insert into Tag(id, text) values(6, 'Tag 6');
insert into Tag(id, text) values(7, 'Tag 7');
insert into Tag(id, text) values(8, 'Tag 8');
insert into Tag(id, text) values(9, 'Tag 9');

insert into FImageTag(fimage_id, tag_id) values(3, 1);
insert into FImageTag(fimage_id, tag_id) values(1, 6);
insert into FImageTag(fimage_id, tag_id) values(5, 6);
insert into FImageTag(fimage_id, tag_id) values(4, 6);
insert into FImageTag(fimage_id, tag_id) values(8, 1);
insert into FImageTag(fimage_id, tag_id) values(1, 9);
insert into FImageTag(fimage_id, tag_id) values(7, 9);
insert into FImageTag(fimage_id, tag_id) values(7, 5);
insert into FImageTag(fimage_id, tag_id) values(5, 8);
insert into FImageTag(fimage_id, tag_id) values(5, 2);
insert into FImageTag(fimage_id, tag_id) values(2, 4);
insert into FImageTag(fimage_id, tag_id) values(4, 6);
insert into FImageTag(fimage_id, tag_id) values(4, 6);
insert into FImageTag(fimage_id, tag_id) values(7, 1);
insert into FImageTag(fimage_id, tag_id) values(2, 3);
insert into FImageTag(fimage_id, tag_id) values(5, 2);
insert into FImageTag(fimage_id, tag_id) values(9, 5);
insert into FImageTag(fimage_id, tag_id) values(7, 5);
insert into FImageTag(fimage_id, tag_id) values(9, 4);
insert into FImageTag(fimage_id, tag_id) values(9, 8);

select * from FImage as f
LEFT JOIN FImageTag as ft ON ft.fimage_id = f.id
LEFT JOIN Tag as t ON ft.tag_id = t.id
LIMIT 5;

 id |        url        | fimage_id | tag_id | id | text  
----+-------------------+-----------+--------+----+-------
  1 | blah.com/im01.jpg |         1 |      6 |  6 | Tag 6
  5 | blah.com/im05.jpg |         5 |      6 |  6 | Tag 6
  4 | blah.com/im04.jpg |         4 |      6 |  6 | Tag 6
  8 | blah.com/im08.jpg |         8 |      1 |  1 | Tag 1
  1 | blah.com/im01.jpg |         1 |      9 |  9 | Tag 9
(5 rows)