I haven't run into this particular challenge before, maybe some of you have?
This is the db structure:
Entity A
Fields: Entity A
Entity B
Fields: Entity B
Associative Table
Fields: Entity A, Entity B, True/False
or with sample data:
Entity A
Entity A:
Cat
Frog
Entity B
Entity B:
Ears
Fur
Associative Table
Entity A: Entity B: True/False:
Cat Ears True
Cat Fur True
Frog Ears True
Frog Fur False
I want a query that returns Ears because Ears is true for Cat AND Frog.
The query should NOT return Fur because Fur is only true for Cat, it is NOT true for Frog.
It was simple enough to write a query that finds B = true for ANY value of A:
In my sample, this returns Ears AND Fur - NOT the result I want.
I can't figure out how to write it so that it only finds the ones that are common to ALL values of A.
My current solution is to loop through two recordsets and test each value individually to manually build a list of common values. This is a really big pain, and I think it will also get slow if the list gets long. Surely there is a logical way to accomplish this in a query, right?
This is the db structure:
Entity A
Fields: Entity A
Entity B
Fields: Entity B
Associative Table
Fields: Entity A, Entity B, True/False
or with sample data:
Entity A
Entity A:
Cat
Frog
Entity B
Entity B:
Ears
Fur
Associative Table
Entity A: Entity B: True/False:
Cat Ears True
Cat Fur True
Frog Ears True
Frog Fur False
I want a query that returns Ears because Ears is true for Cat AND Frog.
The query should NOT return Fur because Fur is only true for Cat, it is NOT true for Frog.
It was simple enough to write a query that finds B = true for ANY value of A:
Code:
SELECT EntityB FROM AssociativeTable INNER JOIN EntityA ON AssociativeTable.EntityA=EntityA.EntityA WHERE TrueFalse=True;"
I can't figure out how to write it so that it only finds the ones that are common to ALL values of A.
My current solution is to loop through two recordsets and test each value individually to manually build a list of common values. This is a really big pain, and I think it will also get slow if the list gets long. Surely there is a logical way to accomplish this in a query, right?