SQL - Counting sets of Field-B values for each Field-A value
- by potrnd
Hello,
First of all sorry that I could not think of a more descriptive title.
What I want to do is the following using only SQL:
I have some lists of strings, list1, list2 and list3.
I have a dataset that contains two interesting columns, A and B. Column A contains a TransactionID and column B contains an ItemID.
Naturally, there can be multiple rows that share the same TransactionIDs.
I need to catch those transactions that have at least one ItemID in each and every list (list1 AND list2 AND list3).
I also need to count how many times does that happen for each transaction.
[EDIT] That is, count how many full sets of ItemIDs there are for each TransactionID", "Full Set" being any element of the list1 with any element of the list2 with any element of the list3
I hope that makes enough sense, perhaps I will be able to explain it better with a clear head.
Thanks in advance