Search code examples
kdb

KDB select where

I have a table 

t:flip `dt`id`data ! (`d1`d1`d2`d2`d3`d3; 0 1 0 1 0 1; 100 200 100 300 0 200)

and from some other query, I have a table 

s:flip `dt`id ! (`d1`d2`d2`d3; 0 0 1 1)

How can I select from t such that it returns all entries where the combination of dt and id are in s, so return

flip `dt`id`data ! (`d1`d2`d2`d3; 0 0 1  1; 100 100 300 200)
Solution

  • You can use in on table to table ops so just create a table from your required columns in t and use in to search s for the corresponding records. As long as the table columns and types from the left argument and right argument are the same, then in will produce a boolean list as expected.

    q)select from t where ([]dt;id) in s
dt id data
----------
d1 0  100
d2 0  100
d2 1  300
d3 1  200