SQLTeam.com | Weblogs | Forums

Exclude transactions with exclusively alcohol items, but include transactions with some or none

Hi guys. Not sure how to explain this, but I'll do my best.

Let's say I have a table called Transactions. Some of these transactions have multiple line items, and those line items are contained in a nested array called Product. The category for each item is also listed in the same nested array. So this is what the table looks like before unnesting:

Transaction_ID Product
1 {product_id, Category}
2 {product_id, Category}
3 {product_id, Category}
4 {product_id, Category}
5 {product_id, Category}

And after Product has been unnested:

Transaction_ID      product_id                Category
1                                 27                        Liquor 
1                                 62                        Produce
1                                 70                        Liquor 
2                                 24                        Bakery
2                                 40                        Pharmacy
3                                 55                        Bakery
4                                 20                        Liquor 
4                                 23                        Produce
5                                 15                        Liquor
5                                 19                        Liquor

Now I'm trying to write a query that excludes transaction IDs where ALL items belong to the Liquor category. However, I want to keep the transactions where only some or none of the items belong to Liquor. In other words, my output should keep transactions IDs 1-4, but it should exclude transaction ID 5. 1 and 4 both have some liquor items, but since they have other items, they should still be included in the results.

So far, I've tried the following queries:

Transaction_ID, product_id, Category
From Transactions, unnest(Product)
Where Category != 'Liquor'

Transaction_ID, product_id, Category
From Transactions, unnest(Product)
Where Category <> 'Liquor'

In both cases, my output only pulls transactions that have no liquor items at all. So it only pulls transaction IDs 2 and 3, while I want to grab 1 and 4 as well. Do I need to unnest the Product array in a subquery first, and then apply the Where condition in the main query?

Any thoughts on this would be much appreciated. Thanks!

This doesn't look like SQL Server.

What dbms are you using? MySQL?

Please be aware that this is a SQL Server forum. Someone here may or may not be able to help you with coding for a different dbms.

Apologies. Wasn't paying attention. Yes, I am using MySQL. Thanks.

I'm a fan of CTE in complex situations. So I would first unnest the product in the WITH statement and then continue. I wasn't sure MySQL is also capable of a CTE but it does:

MySQL :: MySQL 8.0 Reference Manual :: 13.2.15 WITH (Common Table Expressions)