Search code examples
relational-algebrarelationalrelational-division

Relational algebra question (trouble wording, please read description)

Disclaimer: Please forgive my wording as im having alot of trouble trying to describe my question.

I have tables A and B

Table A(ID, YEAR) where the key is ID+YEAR:
ID|YEAR
-------
1 |2001
1 |2002
1 |2003
2 |2001
2 |2002
3 |2001

Table B(YEAR) key is YEAR:
YEAR
----
2001
2002
2003

I want to find the ID that appears in all years of table B which is ID 1.

that is, ID that appears in all YEAR rows of table B.

I have tried using some techniques with join and difference but im having trouble figuring this out.

If there is a better way to word this question, please tell me, because I also can't seem to find a way to ask this online. I don't know how to word it in a concise way.

EDIT: I should mention that this is using relational algebra not sql.

Solution

  • It sounds like you want relational division, geeksforgeeks relational division. Relational division is used when you want to find out which entities interact with all entities of another set.

    You want to take A / B which will return a table of the ID's of A that are associated with ALL the years in B.

    Another example would be if you had a table of books and a table of readers. readers / books would tell you which readers had read all the books.