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:
Select
Transaction_ID, product_id, Category
From Transactions, unnest(Product)
Where Category != 'Liquor'
Select
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!