Inner Join on many to many relationships on the two outer tables of the 5

Question

I have the following tables: tPlans, tPlansProperties, tProperties, tEventsProperties, tEvents.

For these tables there is a many to many relationship between tPlans and tProperties. There is also a many to many relationship between tEvents and tProperties.

I am trying to get all tEvents that have ALL the same tProperties as tPlans, but am running into a dilemma where if there is even one Property that is in both Plans and Events that it shows it as a result. I need to achieve this without hard-coding all the different types of properties or post processing the queried results.

Here is what I have so far:

SELECT 
    P1.id
    ,P1.name
    ,E1.property
    ,E1.id 
    ,E1.name
FROM (
    SELECT 
        P.id
        , P.name
        , PP.property
    FROM tPlans P
    INNER JOIN tPlansProperties PP
    ON P.id = PP.id
) P1

INNER JOIN (
    SELECT 
        E.id
        , E.name
        , EP.property
    FROM tEvents E
    INNER JOIN tEventsProperties EP
    ON E.id = EP.id
    WHERE E.id LIKE 'EVT2011SC99'
) E1
ON P1.property = E1.property

here is a high level view of the tables:

tPlans

id,name,type,cost,vendor_id

tPlansProperties

id,property

tProperties

property,property_name,property_type

tEventsProperties

id,property

tEvents

id,name,description,date,owner_id

My head hurts right now, but I think you may make good use of the [ALL clause](http://msdn.microsoft.com/en-us/library/ms178543(v=sql.105).aspx) in MS SQL Server. — Paul Fleming, Jan 14 '13 at 20:33
@flem MS SQL, but I need to use sql which is database independent. — Dave, Jan 14 '13 at 21:12
Do you have to express this in pure SQL? Is there an application involved that can do the final filtering? — Jim Garrison, Jan 14 '13 at 21:12
@HLGEM it looks like you are making leaps in logic. The application controls this, but the database table specification needed to accommodate for both "quiet" and "queit". This is not a design error, it is intended - I am sorry if you don't have any practical experience in real-world enterprise development which often has odd requirements. — Dave, Jan 14 '13 at 21:15
@dave I have many years of practical exerience in enterprise systems. So sorry to disappoint you. This is a poor design and you will always have difficulty querying it properly. Did you hire a database expert to do your design? If not then you should have. — HLGEM, Jan 14 '13 at 21:20
You might look at this: http://www.elsevierdirect.com/companions/9780123735683/appendices/03~Appendix_C_Set-Comparison_Queries_in_SQL.pdf It contains some interesting insights into expressing subset-comparison queries. It doesn't have THE answer but might point you in the right direction. This is a hard problem to express in SQL. — Jim Garrison, Jan 14 '13 at 21:25

Gordon Linoff · Answer 1 · 2013-01-14T21:24:45.633

The following gets all events that have all the properties in PlanProperties, which seems to be what you are asking for ("I am trying to get all tEvents that have ALL the same tProperties as tPlans"):

select ep.Id
from tEventsProperties ep full outer join
     (select distinct pp.property
      from PlansProperties
      -- where planid = ??
     ) pr
     on ep.property = pp.property
group by ep.Id
having count(*) = count(e.property) and
       count(*) = count(pr.property)

If you want it for a specific plan, then use the where clause in the subquery. If you need more event information, join back in the event table.

This has not been tested.

Getting all the plans that match the properties for a given event is a query like:

select pr.planid
from (select distinct property
      from tEventsProperties ep
      where ep.event = THEEVENT
     ) ep full outer join
     (select distinct planid, pp.property
      from PlansProperties
     ) pr
     on ep.property = pp.property
group by pr.planid
having count(*) = count(ep.property) and
       count(*) = count(pr.property)

I was thinking of something similar, that is counting all the properties of the resulting plan results and from those that were in the result set to rule out rouge matches; I thought there would be a better way thought that would compare the blocks of properties, but I haven't seen anything yet. — Dave, Jan 14 '13 at 21:11
I almost forgot to mention, the planid is not known; this is an attempt to get all possible matching plans for a given event — Dave, Jan 14 '13 at 21:18

score 1 · Answer 2 · answered Jan 14 '13 at 21:39

Gordon was close. To get all matching pairs of events and plans, you need a slightly more complicated set of joins. Here's a solution that seems to work fine on very small data samples. I have no idea how it is going to perform in your environment:

SELECT
  ISNULL(pp.id, pl.id) AS [plan],
  ISNULL(ev.id, ep.id) AS event
FROM
  tPlansProperties pp CROSS JOIN tEvents ev
FULL JOIN
  tEventsProperties ep CROSS JOIN tPlans pl
ON pp.property = ep.property
  AND pp.id = pl.id
  AND ev.id = ep.id
GROUP BY
  ISNULL(pp.id, pl.id),
  ISNULL(ev.id, ep.id)
HAVING COUNT(*) = COUNT(ep.property)
   AND COUNT(*) = COUNT(pp.property)
;

It is available at SQL Fiddle too.