Select parent value if all child values are in a query

Question

I have three tables similar to the example below

T1                                   T3
------------------------             --------------------------
T1ID       X         ...             T3ID          Z        ...
------------------------             --------------------------
   1       aaa                          1          abc
   2       bbb                          2          def
   3       ccc                          3          ghi
   4       ddd                          4          jkl
                                        5          mno


T2
----------------------------------------
T2ID      T1ID      T3ID      Y     ...
----------------------------------------
   1         1         1      eee
   2         2         1      fff
   3         3         1      ggg
   4         2         2      hhh
   5         2         3      iii
   6         3         3      jjj
   7         1         4      kkk
   8         2         4      lll
   9         3         5      mmm
  10         4         5      nnn      

What I want is an inner-join of all 3 tables but I only want the values in T3 selected if all of the values in T2 referencing the selected values in T1 are part of a previously established query. So for example if the previously established query (Q1) has T1ID's 1 and 2 selected then I want this query to select T3ID's 1 and 4. A typical join would select T3ID's 1, 2, 3, and 4 but I do not want T3ID's 2 and 3 selected because the values for T3ID 2 and 3 do not include T1ID 1 in T2. It is okay that T3ID 1 also contains other T1ID's in T2 for it to be selected. (I have no ability to change the database design so please no comments on changing it. Currently this database is in MS Access)

Answer 1

Assuming the values for your initial query result are 1 and 2, you have two values returned back, which you can now plug into a HAVING clause.

You would use this to search for the number of times T3ID appears where it has two matches.

This code is written in T-SQL, but I'm sure you can convert it quite easily to Access, as it's for demonstration purposes anyway.

-- This returns the T3IDs that match the conditions
SELECT T3ID
FROM T2
WHERE T3ID IN (1, 2)
GROUP BY T3ID
HAVING COUNT(T3ID) = 2 -- to match on the number of values

This returns two values: 1 and 4. Now you can plug that result into your existing INNER JOIN query, which returns everything, just adding on a WHERE clause.

For example:

SELECT * FROM T2
WHERE T3ID IN (
    SELECT T3ID
    FROM T2
    WHERE T3ID IN (1, 2)
    GROUP BY T3ID
    HAVING COUNT(T3ID) = 2
)

Answer 2

you need to re-explain with clear output expected in tabular format.

Sample data,

declare @T1  table(T1ID int,X varchar(20))
 insert into @T1 VALUES         
(1,'aaa') ,(2,'bbb')                     
,(3,'ccc') ,(4,'ddd')                     


 declare @T3 table(T3ID int,Z varchar(20))
  insert into @T3 VALUES  
 (1,'abc'),(2,'def')
  ,(3,'ghi'),(4,'jkl'),(5,'mno')

declare @T2 table(T2ID int,T1ID int,T3ID int,Y varchar(10))
insert into @T2 VALUES
( 1,1,1,'eee')
,( 2,2,1,'fff')
,( 3,3,1,'ggg')
,( 4,2,2,'hhh')
,( 5,2,3,'iii')
,( 6,3,3,'jjj')
,( 7,1,4,'kkk')
,( 8,2,4,'lll')
,( 9,3,5,'mmm')
,(10,4,5,'nnn')  

Sql server version

;With CTE as
(
select * from @T1
where T1ID in(1,2)
)
,CTE1 AS(
select t2.T3ID from @T2 t2
inner join cte c
on t2.T1ID=c.t1id
group by t2.T3ID
having count(*)=(select count(*) from cte)
)
select t3.* 
from @T3 t3
inner join cte1 c1 on t3.T3ID=c1.t3id

I have use CTE becasue I don't know what is your previously established query. If i am aware then query may be re-written accordingly

MS Access version I have create and run this query in MS Access with same sample data.

select t3.* from T3 t3

inner join
(
select t2.t3id from T2 t2 
inner join
(
select * from T1
where T1ID in(1,2)
)t4
on t2.T1ID=t4.t1id
group by t2.T3ID
having count(*)=(select count(*) from T1
where T1ID in(1,2))
)t5
on t3.t3id=t5.t3id

where previously establish query is

select * from T1
    where T1ID in(1,2)

you have to worry about this query.