I have queried 3 columns from a table as follows:
lst: distinct select b_market_order_no,instrumentID,mkt from tb where event=`OvernightOrder
based on these I want to query another table and get a dates column from it
select dates from tbp where
I am not quite sure how to apply the where clause or join clause here so values from lst get the corresponding dates column from tbp. Both tb and tbp tables have the same columns, they are created for different days from the same schema.
If I understand your use case correctly then you can use a table in your where clause as follows:
q)show tab1:([]a:1 2 3;b:4 5 6)
a b
---
1 4
2 5
3 6
q)show tab2:([]date:.z.d+1 2 3;a:2 3 4;b:5 6 7)
date a b
--------------
2020.04.29 2 5
2020.04.30 3 6
2020.05.01 4 7
q)select date from tab2 where([]a;b)in tab1
date
----------
2020.04.29
2020.04.30
Basically this builds up a table of the relevant columns from tab2 that are in tab1 and compares them.
If the schema of the table being joined is variable another approach may be required, such as this:
q)select date from tab2 where(cols[tab1]#tab2)in tab1
date
----------
2020.04.29
2020.04.30
Or even using lj and adding an additional Boolean column to mark valid rows in tab1 to select from tab2:
select date from(tab2 lj cols[tab1]xkey update c:1b from tab1)where c