4 (I wasn't familiar with : notation...but put that back in)
source | link

I believe the issue with your current implementation is that you are not looking at other rows which match the same sku/txns, but have a different rk_group on another line matching that sku/txns.

SELECT
    i.sku,
    i.GoodGroup as rk_group
FROM (
    SELECT
        A.sku as sku,
        B.rk_group as GoodGroup,
        COUNT(C.rk_group) as cnt
    FROM
        1_txns as A
        INNER JOIN 2_products as B
            ON A.sku=B.sku
            AND B.rk_group = 'group1':group1 --should eliminate anything that isn't in group 1. If MySQL complains about a constant here, drop it to the `WHERE` clause
         LEFT JOIN 2_products as C
            ON A.sku=C.sku 
            AND B.rk_group <> C.rk_group --don't repeat the same entry as previous. This is redundant given the current WHERE clause
     GROUP BY
         A.sku, B.rk_group
     WHERE
         C.rk_group IN ('group2':group2, 'group3':group3)
) as i
WHERE
    i.cnt < 1

You can wrap another layer around it SELECT COUNT( *stuff from above* ) to get your desired count of number of entries

I believe the issue with your current implementation is that you are not looking at other rows which match the same sku/txns, but have a different rk_group on another line matching that sku/txns.

SELECT
    i.sku,
    i.GoodGroup as rk_group
FROM (
    SELECT
        A.sku as sku,
        B.rk_group as GoodGroup,
        COUNT(C.rk_group) as cnt
    FROM
        1_txns as A
        INNER JOIN 2_products as B
            ON A.sku=B.sku
            AND B.rk_group = 'group1' --should eliminate anything that isn't in group 1. If MySQL complains about a constant here, drop it to the `WHERE` clause
         LEFT JOIN 2_products as C
            ON A.sku=C.sku 
            AND B.rk_group <> C.rk_group --don't repeat the same entry as previous. This is redundant given the current WHERE clause
     GROUP BY
         A.sku, B.rk_group
     WHERE
         C.rk_group IN ('group2', 'group3')
) as i
WHERE
    i.cnt < 1

You can wrap another layer around it SELECT COUNT( *stuff from above* ) to get your desired count of number of entries

I believe the issue with your current implementation is that you are not looking at other rows which match the same sku/txns, but have a different rk_group on another line matching that sku/txns.

SELECT
    i.sku,
    i.GoodGroup as rk_group
FROM (
    SELECT
        A.sku as sku,
        B.rk_group as GoodGroup,
        COUNT(C.rk_group) as cnt
    FROM
        1_txns as A
        INNER JOIN 2_products as B
            ON A.sku=B.sku
            AND B.rk_group = :group1 --should eliminate anything that isn't in group 1. If MySQL complains about a constant here, drop it to the `WHERE` clause
         LEFT JOIN 2_products as C
            ON A.sku=C.sku 
            AND B.rk_group <> C.rk_group --don't repeat the same entry as previous. This is redundant given the current WHERE clause
     GROUP BY
         A.sku, B.rk_group
     WHERE
         C.rk_group IN (:group2, :group3)
) as i
WHERE
    i.cnt < 1

You can wrap another layer around it SELECT COUNT( *stuff from above* ) to get your desired count of number of entries

3 coming at this from another angle
source | link

I believe the issue with your current implementation is that you are not looking at other rows which match the same sku/txns, but have a different rk_group on another line matching that sku/txns.

SELECT
    COUNTi.sku,
    i.GoodGroup as rk_group
FROM (DISTINCT
    SELECT
        A.txn_idsku as sku,
        B.rk_group as GoodGroup,
        COUNT(C.rk_group) as cnt
    FROM
        1_txns as A 
 INNER JOIN
      INNER JOIN 2_products as B ON
            ON A.sku=B.sku
WHERE
    B.rk_group = 'group1' AND
    NOT EXISTSAND (
B.rk_group = 'group1' --should eliminate anything that isn't SELECTin 1group FROM1. (
If MySQL complains about a constant here, drop it to the `WHERE` SELECTclause
         LEFT JOIN 2_products as C
   B2.rk_group
          ON A.sku=C.sku FROM
             AND B.rk_group <> 2_productsC.rk_group as--don't B2
repeat the same entry as previous. This is redundant given the current WHERE clause
      GROUP BY
         B2.sku=BA.sku, ANDB.rk_group
       WHERE
         B2C.rk_group IN ( 'group2', 'group3')
) as i
WHERE
  ) as i).cnt < 1

You can wrap another layer around it SELECT COUNT( *stuff from above* ) to get your desired count of number of entries

I believe the issue with your current implementation is that you are not looking at other rows which match the same sku/txns, but have a different rk_group on another line matching that sku/txns.

SELECT
    COUNT(DISTINCT A.txn_id)
FROM 1_txns as A INNER JOIN
     2_products as B ON
         A.sku=B.sku
WHERE
    B.rk_group = 'group1' AND
    NOT EXISTS (
        SELECT 1 FROM (
            SELECT
                B2.rk_group
            FROM
                2_products as B2
            WHERE 
                B2.sku=B.sku AND
                B2.rk_group IN ( 'group2', 'group3')
    ) as i)

I believe the issue with your current implementation is that you are not looking at other rows which match the same sku/txns, but have a different rk_group on another line matching that sku/txns.

SELECT
    i.sku,
    i.GoodGroup as rk_group
FROM (
    SELECT
        A.sku as sku,
        B.rk_group as GoodGroup,
        COUNT(C.rk_group) as cnt
    FROM
        1_txns as A 
        INNER JOIN 2_products as B
            ON A.sku=B.sku
            AND B.rk_group = 'group1' --should eliminate anything that isn't in group 1. If MySQL complains about a constant here, drop it to the `WHERE` clause
         LEFT JOIN 2_products as C
            ON A.sku=C.sku 
            AND B.rk_group <> C.rk_group --don't repeat the same entry as previous. This is redundant given the current WHERE clause
     GROUP BY
         A.sku, B.rk_group
     WHERE
         C.rk_group IN ('group2', 'group3')
) as i
WHERE
    i.cnt < 1

You can wrap another layer around it SELECT COUNT( *stuff from above* ) to get your desired count of number of entries

2 added 5 characters in body
source | link

I believe the issue with your current implementation is that you are not looking at other rows which match the same sku/txns, but have a different rk_group on another line matching that sku/txns.

SELECT
    COUNT(DISTINCT A.txn_id)
FROM 1_txns as A INNER JOIN
     2_products as B ON
         A.sku=B.sku
WHERE
    B.rk_group = 'group1' AND
    NOT EXISTS (
        SELECT 1 FROM (
            SELECT
                B2.rk_group
            FROM
                2_products as B2
            WHERE 
                B2.sku=B.sku AND
                B2.rk_group IN ( 'group2', 'group3')
    ) as i)

I believe the issue with your current implementation is that you are not looking at other rows which match the same sku/txns, but have a different rk_group on another line matching that sku/txns.

SELECT
    COUNT(DISTINCT A.txn_id)
FROM 1_txns as A INNER JOIN
     2_products as B ON
         A.sku=B.sku
WHERE
    B.rk_group = 'group1' AND
    NOT EXISTS (
        SELECT 1 FROM (
            SELECT
                B2.rk_group
            FROM
                2_products as B2
            WHERE 
                B2.sku=B.sku AND
                B2.rk_group IN ( 'group2', 'group3')
    ))

I believe the issue with your current implementation is that you are not looking at other rows which match the same sku/txns, but have a different rk_group on another line matching that sku/txns.

SELECT
    COUNT(DISTINCT A.txn_id)
FROM 1_txns as A INNER JOIN
     2_products as B ON
         A.sku=B.sku
WHERE
    B.rk_group = 'group1' AND
    NOT EXISTS (
        SELECT 1 FROM (
            SELECT
                B2.rk_group
            FROM
                2_products as B2
            WHERE 
                B2.sku=B.sku AND
                B2.rk_group IN ( 'group2', 'group3')
    ) as i)
1
source | link