How can we select rows based on the column condition in MySQL?-CodePudding

We have a table like the following:

id	transaction_id	item
2100	20010904	item1
2100	20010904	item2
2100	20010904	item3
2101	20010904	item1
2101	20010904	item4
2102	20010904	item1
2102	20010904	item2
2102	20010904	item3
2102	20010905	item4

id is the people's id, transaction_id is the id for that transaction for that person; item are the items included in that transaction.

I want to select rows which item not contain item1 and item4 together in the same transaction_id.

So the expected results will be:

id	transaction_id	item
2100	20010904	item1
2100	20010904	item2
2100	20010904	item3
2102	20010904	item1
2102	20010904	item2
2102	20010904	item3
2102	20010905	item4

I know how to select rows that item only contains 'item1', but I am not sure about how to select rows that item not contain 'item1' and 'item4' in the same transaction_id. In fact, the cases are more complicate. In some cases, I want to select rows that item does not contain 'item1','item4','item5','item6' in the same transaction_id.

CodePudding user response：

You could use a left anti-join approach:

SELECT id, transaction_id, item
FROM yourTable t1
LEFT JOIN
(
    SELECT id
    FROM yourTable
    WHERE item IN ('item1', 'item4')
    GROUP BY id
    HAVING MIN(item) <> MAX(item)
) t2
    ON t2.id = t1.id
WHERE
    t2.id IS NULL;

CodePudding user response：

Having COUNT() with GROUP BY can help in your case:

SELECT id, transaction_id, item 
FROM TransactionTable
WHERE id IN (
    SELECT id
    FROM TransactionTable
    WHERE item IN ('item1', 'item4')  
    GROUP BY id
    HAVING COUNT(DISTINCT item) != 2
) Q

CodePudding user response：

SELECT * FROM TABLE a 
WHERE CONCAT(a.transction_id ,if(substr(a.item,-1)>3,'item',a.item ) NOT IN (

  (SELECT 
    CONCAT('item',transction_id) FROM TABLE a  
     GROUP BY transaction_id HAVING  COUNT(*)>3 /* total filted count*/
    )
)

CodePudding user response：

select * from (
select id, item, count(case when item in ('item1','item4') then 1 else 0 end ) over (partition by transaction_id, id) as cate
from yourtable) where cate <>2;