3

I am trying to filter a table which has NON-UNIQUE transaction id's, joined to a products table on 1 common column. The filter needs to be as follows:

equal to :group1 AND
NOT equal to :group2 AND
NOT equal to :group3

I originally started with the below query but without the AND rk_group <> ____ conditions.

SELECT COUNT(DISTINCT txn_id)
                    FROM 1_txns
                    INNER JOIN 2_products USING (sku)
                    WHERE rk_group = :group1
                    AND rk_group <> :group2
                    AND rk_group <> :group3
                    ;

I have also tried

SELECT COUNT(DISTINCT txn_id)
                    FROM 1_txns
                    INNER JOIN 2_products USING (sku)
                    WHERE rk_group NOT IN ( :group2, :group3)
                    ;

I have also tried multiple combinations of joins and IN() and NOT IN() and it's still returning all teams ids including those where the NOT groups are present.

Can someone point me in the right direction please.

Schema info relevant for the query:

table 1_txns
(txn_id, sku)

table 2_products
(sku, rk_group)

Sample data

Txn_id, rk_group


------

    1,group1
    1,group2
    2,group1
    3,group1
    3,group3

If the above were my data, and group1 is my = group, and groups2 and 3 are my != groups, then I should return a row count of 1. Which is txn id '2'. All methods I've tried so far will return a count of 3.

NOTE: there are only 3 distinct rk_groups in the 2_products table.

  • It might be helpful if you elaborated on what you mean by "no luck" Are you getting too many results, too few? One thing that I see is that you aren't specifying the join logic for 1_txns and 2_products. How are those two tables related? – Jonathan Fite Dec 17 '15 at 17:06
  • Thanks and apologies. I have updated my question. I am still getting too many results. Is 'USING (sku)' not appropriate join logic? – Adam Copley Dec 17 '15 at 17:17
  • It would be helpful if you give a sample data, and tell which rows should not be selected. – Jehad Keriaki Dec 17 '15 at 17:45
  • I have updated the question, excuse the formatting I'm on my mobile – Adam Copley Dec 17 '15 at 17:52
  • I'm finding this confusing. Your query shows group1 as an inequality, yet you say at the bottom of your post that group1 is your equality group. You've only included sample data from one table, but there are two in your query. Based on the data you've provided at this point, the count of 3 you're receiving looks correct, as there are 3 distinct txn_id values with a rk_group of group1. – Dan says GoFundMonica Dec 17 '15 at 18:32
6

Your WHERE clause does not make much sense, because it is applied to each row individually and it is pointless to check if the same value is equal to a and at the same time not equal to b or c – of course, it will not be equal to b or c if it is a. What you want instead, therefore, is for the conditions to be applied to a group of rows as a whole – more specifically, to each group of rows sharing the same txn_id.

So, you need to use GROUP BY and, to apply conditions to groups of rows, HAVING. This query will give you the list of txn_id values matching your requirements:

SELECT
    t.txn_id
FROM
    1_txns AS t
    INNER JOIN 2_products AS p USING (sku)
GROUP BY
    t.txn_id
HAVING
    COUNT(p.rk_group = :group1 OR NULL) > 0
    AND COUNT(p.rk_group IN (:group2, group3) OR NULL) = 0
;

As you do not seem to want the list, only the number of its items, use the above as a derived table to count the rows:

SELECT
   COUNT(*)
FROM
(
    SELECT
        txn_id
    FROM
        1_txns AS t
        INNER JOIN 2_products AS p USING (sku)
    GROUP BY
        t.txn_id
    HAVING
        COUNT(rk_group = :group1 OR NULL) > 0
        AND COUNT(rk_group IN (:group2, group3) OR NULL) = 0
) AS s
;

As you can see, COUNT(DISTINCT ...) is not necessary: the derived table is grouping by txn_id and thus cannot return duplicates – so, COUNT(*) is enough to get the correct result.

In case you are not aware, the OR NULL bit lets the COUNT function count only matches and omit mismatches, as explained in detail in this answer:

  • 1
    Excellent answer and explanation. Thank you! – Adam Copley Dec 17 '15 at 21:07
  • 1
    @RickJames it converts false to null so COUNT() doesn't couth them (the falses), only the trues. – ypercubeᵀᴹ Dec 18 '15 at 7:24
  • 2
    @AndriyM you could "simplify" COUNT(BooleanExpression OR NULL) to SUM(BooleanExpression) in mysql. – ypercubeᵀᴹ Dec 18 '15 at 7:26
  • 2
    @Sillycubeᵀᴹ: Very true, and I've alternately suggested the former and the latter in my answers (sometimes both). COUNT(boolean OR NULL) would be my personal preference, because it expresses my intention of counting better (or so I think), but I accept that there are probably many people who prefer using SUM(boolean) – and I can understand why they would. – Andriy M Dec 18 '15 at 7:48
  • 1
    @AdamCopley: If both A and B are mandatory to be present, then with the method in my answer you'll need to have a separate condition for each: COUNT(grp = A OR NULL) > 0 AND COUNT(grp = B OR NULL) > 0 (followed by AND COUNT(grp = C OR NULL) = 0 to account for not C). Otherwise you could use COUNT slightly differently and cover both A and B with one condition: COUNT(DISTINCT CASE WHEN grp in (A, B) THEN grp END) = 2 – Andriy M Dec 18 '15 at 10:22
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.