I have a table:
t value
---------
A MT
A RX
B SD
B RX
A RX
C SD
I want to select common values for t=A with other t but without duplicates:
Expected output:
t value
---------
A RX
B RX
This is a long winded solution so let me break it down
q)distinct select from tab where val in exec val from select from tab where t<>`A,val in exec val from tab where t=`A
t val
-----
A RX
B RX
In the first part we exec values where t=`A
q)exec val from tab where t=`A
`MT`RX`RX
We use this list in the next part of our query which selects from our table where values are in this list, and where t<>`A
q)select from tab where t<>`A,val in exec val from tab where t=`A
t val
-----
B RX
We use the values from our previous query to select from our table
q)select from tab where val in exec val from select from tab where t<>`A,val in exec val from tab where t=`A
t val
-----
A RX
B RX
A RX
Finally, use distinct to remove duplicates
q)distinct select from tab where val in exec val from select from tab where t<>`A,val in exec val from tab where t=`A
t val
-----
A RX
B RX
Note: Since value is a q key word, it’s advised not to use it as a column name in a table.