create database if not exists test;

drop table if exists files;
create table files (
  id int(11) auto_increment,
  filename varchar(128),
  primary key  (id)
)  engine = innodb ;

drop table if exists string_tag;
create table string_tag (
  id int(11) auto_increment,
  file_id int(11),
  stringtag varchar(128),
  primary key  (id),
  foreign key (file_id) references files(id)
) engine = innodb ;

drop table if exists int_tag;
create table int_tag (
  id int(11) auto_increment,
  file_id int(11),
  inttag int(11),
  primary key  (id),
  foreign key (file_id) references files(id)
) engine = innodb ;

insert  into files(id,filename) values (1,'poop.txt');
insert  into files(id,filename) values (2,'pirates.doc');

insert  into string_tag(id,file_id,stringtag) values (1,2,'pirate');
insert  into string_tag(id,file_id,stringtag) values (2,2,'pirates');
insert  into string_tag(id,file_id,stringtag) values (3,2,'hyarr');
insert  into string_tag(id,file_id,stringtag) values (4,2,'matee');
insert  into string_tag(id,file_id,stringtag) values (5,1,'brown');
insert  into string_tag(id,file_id,stringtag) values (6,1,'loaf');
insert  into string_tag(id,file_id,stringtag) values (7,1,'cornhole');

insert  into int_tag(id,file_id,inttag) values (1,1,13);
insert  into int_tag(id,file_id,inttag) values (2,1,15);
insert  into int_tag(id,file_id,inttag) values (3,1,14);
insert  into int_tag(id,file_id,inttag) values (4,2,276);
insert  into int_tag(id,file_id,inttag) values (5,2,265);
insert  into int_tag(id,file_id,inttag) values (6,2,275);

select filename from files
left outer join string_tag on string_tag.file_id=files.id
left outer join int_tag on int_tag.file_id=files.id
where stringtag='pirates' or stringtag='are' or stringtag='cool'
group by filename;