Re: comparing multiple records in sub-tables

[Peter Brawley]

You show three tables associating respectively authors & programs, papers & authors, programs & papers. That much is clear.

What's not clear: (i) what a "sub-table" is, and (ii) what "marked" refers to. I can guess that a "sub-table" is a set of rows, ie it's meaningless, but "marked" stumps me entirely. So I don't understand the requirement.

So all I can do is suggest a pair of left joins, from paper_authors to paper_programs, and from paper_authors & paper_programs to paper_programs, that should help you suss out what you need ...

select pa.paper, pa.author_user, pp.paper, pp.program, ap.user, ap.program
from paper_authors pa
left join paper_programs pp using(paper)
left join author_programs ap on pa.author_user=ap.user and pp.program=ap.program;