question

starkst avatar image
starkst asked

Query to return set (of values) associated with all distinct row values of another column

Table (Columns C1,C2) ***C1 C2*** A 21 B 15 A 15 A 33 C 21 B 33 B 79 C 15 A 08 C 33 The values in column C1 can be any number of different values as can C2. Ultimately, I want to know which values in C2 are found associated with (in records with) all the different values in C1. (How many values from C2 are with A, B and C). In the table above, these values from C2 are found with: 21: A, C 15: A, B, C 33: A, B, C 79: B 08: A How do I get this? Is it a pivot? Ultimately, the answer I want is query returning 15 and 33 (the only ones in all the different values shown in C1) but getting this result set above would be a big step in that direction. I’m drawing a blank. Any help?
pivotquery-hint
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

starkst avatar image
starkst answered
I didn't know you could do that. That was easier than thinking. And it works, almost. I mistakenly marked the answer as correct and complete after trying it once. I didn't have this example in the original but there could be multiple records of, for example: A 15 A 15 This would be counted in the count (but that is incorrect). Using your example, I think the correct answer is.. select C2 from [YourTable] group by C2 having count(***distinct C1***)= (select count(distinct C1) from data) This works showing all the values (in C2) associated with ALL the distinct values in C1. FYI, I'm using it to perform an AND operation on Rule matching across multiple rules. With this I can evaluate multiple rules in a single procedure. Thanks.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, that would sort that problem.
0 Likes 0 ·
Mister Magoo avatar image
Mister Magoo answered
That wouldn't actually be all that much help to find the answer you want... This will... select C2 from [YourTable] group by C2 having count(*)=(select count(distinct C1) from data)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.