SQL, comparing relationships placed in multiple link tables

Question

(It’s hard for me to even explain this question)

I have 5 tables, A , AZ , Z , BZ , B

AZ is a many-to-many relationship between A and Z BZ represents the many-to-many relationship between B and Z

I need a result set that is AB , where B is associated with the complete set Z for given A

So, let's say that Z is a tag table and contains:

 Tag1 Tag2 Tag3 

A is a table of objects and contains:

 ObjectA ObjectB ObjectC 

B is a widget table and contains:

 WidgetX WidgetY WidgetZ 

AZ contains:

 ObjectA Tag1 ObjectA Tag2 ObjectB Tag3 ObjectC Tag2 ObjectC Tag3 

BZ contains:

 WidgetX Tag1 WidgetX Tag2 WidgetY Tag2 WidgetY Tag3 WidgetZ Tag3 

I want my result set to be:

 ObjectA WidgetX ObjectB WidgetY ObjectB WidgetZ ObjectC WidgetY 

Is this feasible in the query or do I need to undo some intermediate datasets and iterate through the code?

Answer 1

Ok, the main change for rejigger is based on your feedback. These are a little shreds, but here you go:

 select distinct x.aid, x.bid from (select az.aid, bz.bid, (select COUNT(1) from az az1 where az1.aid = az.aid) as acount, (select COUNT(1) from bz bz1 where bz1.zid in (select zid from az az1 where az1.aid = az.aid) and bz1.bid = bz.bid) as bcount from az inner join bz on az.zid = bz.zid ) x where x.acount = x.bcount 

It uses nasty subqueries to get the correct counts, and then limits our rowset based on your criteria.

Answer 2

The main change:

As Andrei rightly points out, I had the wrong end of the stick on my first move. This should be better:

 select a, bb from az cross join b left outer join bz on az.z = bz.z and bb = bz.b group by a, bb having sum(case when bz.b is null then 1 else 0 end) = 0 

The cross join establishes a table in which the assumption is expressed that each b satisfies the condition for each a of AZ. The left outer join checks this assumption and leaves a null value in the bz.b column, where this assumption fails. The having clause excludes ab pairs that contain one or more of these zeros.