1

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Kevin
  • 23
  • 4
  • 1
    It is hard to get hits googling (SO search is poor) relational-algebra relational-division questions even if you know what relational division is. There are many for SQL. This is probably because people phrase their questions poorly. Force yourself to actually write out what you mean with sufficient redrafting & without specific names. Eg return subrows of a table that appear in that table with every row that appears in another table. PS Do you really want what you say? Because if there are no years, you'r asking for all ids, not none. – philipxy Oct 07 '19 at 21:42
  • PS You were assigned a textbook or should be following one & this kind of problem is probably addressed in it. PS There are many RAs (relational algebras). They differ in operators & even what a relation is. Give a reference and/or definition for yours. PS We don't need constraints (including CKs, FKs & cardinalities) to query. When there are constraints there will be additional expressions returning the right answer that otherwise wouldn't. What is necessary & sufficient are table (base & result) meanings. [Re relational querying.](https://stackoverflow.com/a/24425914/3404097) – philipxy Oct 07 '19 at 21:46
  • Thank you all for your comments! – Kevin Oct 07 '19 at 23:05

1 Answers1

3

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.

Philip Nelson
  • 1,027
  • 12
  • 28