Author -> author_tag -> Tag

Many-to-many relationships provide additional opportunities for linking. For a simple author/tag link, you could list authors which have: * All [these tags] * None of [these tags] * Any of [these tags] * One of [these tags] Each of these modes requires grouping on the main table, after which additional operations can be performed using aggregate functions: * all => sum(case field when value then 1 else 0) having field > 0 for values * none => sum(case field when value then 1 when value2 then 1 else 0) for values having sum = 0 * any => sum(case field when value then 1 when value2 then 1 else 0) for values having sum > 0 * one => sum(case field when value then 1 else 0) having (sum field) + (sum field2) = 1 for values

 select         ar.idarticle
 from           article ar
 inner join     article_author aa on aa.idarticle = ar.idarticle
 inner join     (
        select          at.idauthor
        from            author_tag at
        inner join      tag t on t.idtag = at.idtag
        group by        at.idauthor
        having          sum(case t.name when 'staff' then 1 else 0 end) > 0
        and             sum(case t.name when 'editor' then 1 else 0 end) > 0
 ) as x on aa.idauthor = x.idauthor
 where  title like 'Test%';

Start off with all intermediate tables included. A later optimisation pass could perhpas elide any tables which do not contribute to the final result.

Article -> Author -> Address -> City -> Tag

Project -> Issue -> Email -> Commit -> Tag

create table author ( idauthor bigserial primary key, name text ); create index a_name on author(name);

create table article ( idarticle bigserial primary key, title text, content text ); create index a_title on article(title);

create table tag ( idtag bigserial primary key, name text ); create index t_name on tag(name);

create table article_tag ( idarticle_tag bigserial primary key, idarticle bigint references article(idarticle) on delete cascade on update cascade, idtag bigint references tag(idtag) on delete cascade on update cascade ); create index at_article on article_tag(idarticle); create index at_tag on article_tag(idtag); create table author_tag ( idauthor_tag bigserial primary key, idauthor bigint references author(idauthor) on delete cascade on update cascade, idtag bigint references tag(idtag) on delete cascade on update cascade ); create index aut_author on author_tag(idauthor); create index au_tag on author_tag(idtag);

create table article_author ( idarticle_author bigserial primary key, idarticle bigint references article(idarticle) on delete cascade on update cascade, idauthor bigint references author(idauthor) on delete cascade on update cascade ); create index aa_article on article_author(idarticle); create index aa_author on article_author(idauthor);