Get Rows that are include one many to many. but not another

I am having a little trouble with this query. I want to filter my Features down for all features that have applicabilities that have include the name ‘G6’, but that also do not have a many to many relationship with applicabilies that have the name ‘n2’. I have right now:

SELECT inner.* 
FROM 
    (SELECT feat.* 
    FROM Features feat
        INNER JOIN Feature _has_Applicability feat_app 
            ON feat_app.feature_id = feat.id
        INNER JOIN Applicability app 
            ON feat_app.applicability_id = app.id 
                AND app.name like '%G6%'
    WHERE feat.deleted_time = '0000-00-00 00:00:00'
    GROUP BY feat.id
    ) AS inner
    INNER JOIN Feature_has_Applicability out_feat_app 
        ON out_feat_app.feature_id = inner.id
    INNER JOIN Applicability app 
        ON out_feat_app.applicability_id = app.id 
            AND app.name NOT LIKE '%N2%'
GROUP BY inner.id
HAVING count (*) = 1

I have a many to many from Feature to Applicability where

Feature id INT PRIMARY deleted_time DATETIME

Applicability id INT Primary name VARCHAR(45)

Feature_has_Applicability feature_id INT applicability_id INT

Example:
I have feature A with applicabilities named N2 and G6
I have feature B with applicability G6, N7
I have feature C with applicability N2

I want only feature B to be returned as it includes G6 but not N2.

G6 is A and N2 is B in regards to features that have a many to many relationship with them.

This still seems to return features that have an applicability to ‘n2’. Can you see what I am doing wrong? Thank you.

Answer

Your first sub-query seems fine. Personally, I’m not sure why you still get ‘n2’ records based on this query alone without seeing the database. Maybe because you use an upper case ‘N2’ in the query? Operators are case sensitive.

Although I suggest you to use NOT EXISTS. It’d make the code much more understandable with its intention. Try this:

SELECT *
FROM 
  features feat
  INNER JOIN feature_has_applicability feat_app ON feat_app.feature_id = feat.id
  INNER JOIN applicability app ON app.id = feat_app.applicability_id
    AND app.name LIKE '%G6%'
WHERE
  feat.delete_time = '0000-00-00 00:00:00'
  AND NOT EXISTS (
    SELECT
       *
    FROM
      feature_has_applicability out_feat_app
      INNER JOIN applicability out_app ON out_app.id = out_feat_app.applicability_id
        AND app.name LIKE '%N2%'
    WHERE
      out_feat_app.feature_id = feat.id
   )

Using NOT EXISTS help to streamline the code. So in this case, the main query is much easier to understand, where we want to find Feature records that have a ‘G2’ applicability. But then we only want records that do NOT have ‘N2’ applicability owned by the selected Feature records’ ID.

I’m confused with the purpose of GROUP BY and HAVING count(*) = 1. If you group it by its ID and expect each group to only have one record, then doesn’t that mean each filtered Feature record only has a single ‘G6’ Applicability record and thus you don’t need to worry about filtering out ‘N2’ records? Unless there are weird cases like having both ‘G6’ and ‘N2’ keywords in the same Applicability record.

Another pointer, for your sub query, you should never use reserved keywords as an identifier. In this case, you called your sub-query as “inner”, which is a bad practice and may not run at all in other database engines. Perhaps you can call it as “g2_feature”.